Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Problems in creating a DB2 UDF .. Please help

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
r2k1984

New User


Joined: 21 Jun 2005
Posts: 69
Location: chennai

PostPosted: Tue Apr 06, 2010 6:13 pm    Post subject: Problems in creating a DB2 UDF .. Please help
Reply with quote

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
View user's profile Send private message

agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 686
Location: Earth

PostPosted: Tue Apr 06, 2010 11:06 pm    Post subject:
Reply with quote

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
View user's profile Send private message
r2k1984

New User


Joined: 21 Jun 2005
Posts: 69
Location: chennai

PostPosted: Wed Apr 07, 2010 11:41 am    Post subject: Reply to: Problems in creating a DB2 UDF .. Please help
Reply with quote

Hey,

I removed the orphan brackets ... But not worked till now.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Apr 07, 2010 1:40 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Creating an 'Enter' event with IMS MF... dileep chirasani Java & MQSeries 1 Sat Aug 06, 2016 2:23 am
No new posts Creating a dynamic sort card in JCL u... ChitraChhabra DFSORT/ICETOOL 4 Wed Aug 03, 2016 6:15 pm
No new posts creating a physical sequential file i... Shaheen Shaik JCL & VSAM 8 Wed Jun 29, 2016 3:46 pm
No new posts Faccing issues while creating a GDG V... sravindra_s JCL & VSAM 5 Thu Jun 23, 2016 11:26 am
No new posts Creating a pipe delimited file from C... dmanwi COBOL Programming 1 Sat Mar 19, 2016 10:40 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us