View previous topic :: View next topic
|
Author |
Message |
r2k1984
New User
Joined: 21 Jun 2005 Posts: 69 Location: chennai
|
|
|
|
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.
Code: |
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. |
|
Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
I don't know if this helps but there is an orphan paranthesis in the above code after the ORDER BY CLAUSE. |
|
Back to top |
|
|
r2k1984
New User
Joined: 21 Jun 2005 Posts: 69 Location: chennai
|
|
|
|
Hey,
I removed the orphan brackets ... But not worked till now. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
not only here but also in the db2 forum where you posted this thing that you can not get working, you have not provided UPDATED SQL.
believe that the DECLARE should be after the BEGIN.
Why are you using '{' ?
For any additional help, you will need to
provide us via cut&paste inside CODE labels your latest and greatest. |
|
Back to top |
|
|
|