db2 fx

achiola · #1 (**permalink**) 12-27-11, 14:16

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.

stolze · #2 (**permalink**) 12-27-11, 14:42

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

achiola · #3 (**permalink**) 12-27-11, 14:55

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!!

achiola · #4 (**permalink**) 12-27-11, 14:59

Info: Is DB2 5.4 for iSeries.

achiola · #5 (**permalink**) 12-27-11, 15:18

fixed... I run the same query in iSeries Navigator and work!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech