IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

[SQL0029] INTO clause missing from embedded statement.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
HABBIE

New User


Joined: 08 Jul 2014
Posts: 24
Location: INDIA

PostPosted: Fri Sep 04, 2015 3:54 pm
Reply with quote

HI, I have written lots of RPG/ILE & CL & COBOL programs in iSeries. I never worked on procedures & UDF. Now I'm learning these. I'm trying to create a procedure. This is the code. It's compiled without any errors. While running I got the below error.
CREATE FUNCTION SUS.REWT(p_value DATE)
RETURNS DATE
LANGUAGE SQL
NO EXTERNAL ACTION
F1: BEGIN ATOMIC
DECLARE D1 DATE;
DECLARE return_date DATE;
DECLARE RANDOM_VALUE INTEGER;

SET D1= TO_DATE('1990-01-01','YYYY-MM-DD');
--SET D1 = (SELECT DATE(p_value) FROM SYSIBM.SYSDUMMY1);
SET RANDOM_VALUE = CAST (( RAND () * 10000)as INT );
SET D1 = D1 + RANDOM_VALUE DAYS;
SET return_date=D1;

RETURN return_date;
END
While running, I'm getting the below error: Run: SUS.REWTDATE) [SQL0029] INTO clause missing from embedded statement. I don't understand what is the need of INTO clause here. OS: Windows DB2: DB2/400 V5R4 Client: IBM data studio


After this I tried like this
CREATE FUNCTION SUS.REWT(p_value DATE)
RETURNS DATE
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
FENCED
F1: BEGIN ATOMIC
DECLARE D1 DATE;
DECLARE return_date DATE;
DECLARE RANDOM_VALUE INTEGER;

select char(date('1990-01-01'),iso) INTO D1 from SYSIBM.SYSDUMMY1;

--SET RANDOM_VALUE = CAST (( RAND () * 10000)as INT );

select CAST (( RAND () * 10000)as INT ) INTO RANDOM_VALUE from SYSIBM.SYSDUMMY1;

----SET D1 = D1 + RANDOM_VALUE DAYS;

select (D1 + RANDOM_VALUE DAYS) INTO D1 from SYSIBM.SYSDUMMY1;

--SET return_date=D1;
select return_date INTO D1 from SYSIBM.SYSDUMMY1;

RETURN return_date;
END

Even though the error is same.
can someone help me with this
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Fri Sep 04, 2015 4:48 pm
Reply with quote

Why do you continue to refuse to use code tags for your code? Do you not want help? Using the code tags retains extra spaces, makes your code stand out from your commentary and is easier to read. All this makes it easier for people to read your and understand your posts and thus hepls them to respond.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2637
Location: NYC,USA

PostPosted: Fri Sep 04, 2015 9:05 pm
Reply with quote

Please follow Nic's intructions seriously as they are the rules of this forum.
ibmmainframes.com/faq.php?mode=bbcode

Shouldn't below code be changed to
Code:
select D1 INTO return_date from SYSIBM.SYSDUMMY1;


see this link for the error description. If you want to use a SELECT statement then INTO is must otherwise use a CURSOR if you don't want INTO.
www-01.ibm.com/support/knowledgecenter/#!/SSEPGG_9.1.0/com.ibm.db2.udb.msg.doc/doc/sql0029.htm
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SYNCSORT/ICETOOL JOINKEYS SORT Statem... DFSORT/ICETOOL 13
No new posts DFDSS COPY using Filter REFDT statement JCL & VSAM 2
No new posts SKIP LOCKED DATA in UPDATE statement DB2 9
This topic is locked: you cannot edit posts or make replies. Splice JCL into one record for DD sta... SYNCSORT 2
This topic is locked: you cannot edit posts or make replies. Missing Negative sign in COBOL COBOL Programming 6
Search our Forums:

Back to Top