Hai,

I was trying to create a user defined function in DB2. I was not able to create the function and was getting few errors. Please find the code and the error message.



CREATE FUNCTION DESC_FUNC(v_rpt_id integer, v_desc_typ_cde varchar(1))
RETURNS VARCHAR(32672)
DECLARE v_desc VARCHAR(32672) default '';
for1: BEGIN ATOMIC
FOR tmp_row AS
{
SELECT
desc_seq_num, evnt_desc_txt
FROM
"t_flt_sfty_desc DES","t_flt_sfty_rpt rptin1"
WHERE DES.EVNT_DTE=rptin1.EVNT_DTE
AND DES.EVNT_SEQ_NUM=rptin1.EVNT_SEQ_NUM
AND DES.RPT_EVNT_ORD_NUM = rptin1.RPT_EVNT_ORD_NUM
AND DES.RPTEE_TYP_CDE = rptin1.RPTEE_TYP_CDE
AND DES.View_typ_cde = 'D'
AND desc_typ_cde = v_desc_typ_cde
AND rpt_id= v_rpt_id
ORDER BY desc_seq_num
)
}
DO
IF desc_seq_num =1 THEN
SET v_desc = tmp_row.evnt_desc_txt;
ELSE
SET v_desc = v_desc ||tmp_row.evnt_desc_txt;
END IF;
END FOR for1;
return v_desc;
END


I got the following error message

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "V_DESC" was found following "". Expected
tokens may include: "ON AFTER <INTEGER>". SQLSTATE=42601



Can any one please solve this.

Declare your variable inside the BEGIN block, not outside.

Andy

Thanks Andy.

I tried executing the query like below.

CREATE FUNCTION DESC_FUNC(v_rpt_id integer, v_desc_typ_cde varchar(1))
RETURNS VARCHAR(32672)

for1: BEGIN ATOMIC
DECLARE v_desc VARCHAR(32672) default '';
FOR tmp_row AS
{
SELECT
desc_seq_num,varchar(evnt_desc_txt) evnt_desc_txt
FROM
db2fsr.t_flt_sfty_desc DES,db2fsr.t_flt_sfty_rpt rptin1
WHERE DES.EVNT_DTE=rptin1.EVNT_DTE
AND DES.EVNT_SEQ_NUM=rptin1.EVNT_SEQ_NUM
AND DES.RPT_EVNT_ORD_NUM = rptin1.RPT_EVNT_ORD_NUM
AND DES.RPTEE_TYP_CDE = rptin1.RPTEE_TYP_CDE
AND DES.View_typ_cde = 'D'
AND desc_typ_cde = 'D'
AND rpt_id= 11136
ORDER BY
desc_seq_num
)
}
DO
IF desc_seq_num =1 THEN
SET v_desc = tmp_row.evnt_desc_txt;
ELSE
SET v_desc = v_desc ||tmp_row.evnt_desc_txt;
END IF;
END FOR for1;
return v_desc;
END


Iam getting the following error again

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token ":" was found following "". Expected tokens may
include: "ON AFTER <INTEGER>". SQLSTATE=42601

Your syntax is wrong. Use the manuals for the correct syntax. What are the squiggle brackets for?

Andy

Yet another approach is to search for "string concatenation". Because what you try to do is a simple recursive query or you could use the XMLAGG function.

The function body may be written by a RETURN statement, like this...
(DB2 for LUW 9.5 or later)
If you are using DB2 for LUW 9.1 or earlier,
you can use XMLSERIALIZE and XMLAGG instead of XMLCAST and XMLGROUP.

Thanks Guys for the help. But nothing above worked for me.

I tried executing my first query without ). Iam getting the same error.

Iam using db2 8 so XMLCAst will not work ..I tried executing the above query but i was getting the below error.

B21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returnedQL0104N An unexpected token "(" was found following "". Expected tokens may include: "ON AFTER <INTEGER>". SQLSTATE=42601

SQL0104N An unexpected token "(" was found following "". Expected tokens may include: "ON AFTER <INTEGER>

You'll find some working examples here: How to concatenate rows together? | db2ude (I searched for "DB2 string concatenation recursion")