Results 1 to 5 of 5

Thread: db2 fx

  1. #1
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Unanswered: db2 fx

    Hi.
    function:
    Code:
    SET PATH='DB2DTAMART';
    SET SCHEMA='DB2DTAMART';
    
    
    CREATE FUNCTION SieConSoc ( convenio decimal(10,0) ) 
    returns CHAR(3000)
    LANGUAGE SQL 
    BEGIN ATOMIC
    DECLARE v_fullname CHAR(3000); 
    FOR v_row AS 
    	select	 abALPH 
    	from	pd812dta.f55g201 a3
    				left outer join pd812dta.f55g202 a1
    					on a3.dcZNCTOIN = a1.acZNCTOIN
    
    				left outer join pd812dta.f0101 a2
    					on a1.acAN8 = a2.abAN8
    	where dcZCNVIN = convenio
    	DO 
    	SET v_fullname = RTRIM(v_fullname) || '-' || RTRIM(v_row.abALPH); 
    END FOR; 
    
    RETURN v_fullname;
    
    END
    return this error:
    Code:
    Query failed to execute. Execute Query failed. 
    Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [42000:-104: on SQLHANDLE] [IBM][Controlador ODBC de iSeries Access][DB2 UDB]SQL0104 - Símbolo <FIN DE SENTENCIA> no válido. Símbolos válidos: ;.
    Any idea?
    TIA
    Abel.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You have some sort of syntax error (SQLCODE -104). Are you using ';' as statement delimiter or some other character? It must not be ';' because that is used inside the BEGIN ATOMIC ... END and the actual CREATE FUNCTION statement should use some other delimiter.

    Aside from that, is this really your complete function? I'm asking because the function could easily be simplified.
    - you use a loop and only pick v_fullname and abALPH from the last row that was fetched -> use a descending sorting and fetch the first row only (no loop needed)
    - you could also use FETCH FIRST 1 ROW ONLY
    - you have no ORDER BY in your query, so DB2 is free to return the rows in any arbitrary order -> your results are not predictable and can change from call to call
    - I always try to avoid procedural processing; it makes the optimizer's job much harder to optimize the overall SQL statement in which you may use this function -> use set-oriented processing instead
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    Hi and very thanks for your replay. Now I'm not sure what is the statement delimiter. How i can set this? (or verify what's).

    Yes this is the complete function. The idea is this: I have a table with contracts and another with the partners.

    Contract partners
    1 Abel
    1 Pepe
    1 Juan
    2 P1
    2 P2

    The result expected is:
    contract partners
    1 Abel - Pepe - Juan
    2 P1 - P2

    Thanks again!!

  4. #4
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    Info: Is DB2 5.4 for iSeries.

  5. #5
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    fixed... I run the same query in iSeries Navigator and work!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •