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
 

 

[SQL0029] INTO clause missing from embedded statement.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: [SQL0029] INTO clause missing from embedded statement.
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: 1717
Location: UK

PostPosted: Fri Sep 04, 2015 4:48 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

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

Please follow Nic's intructions seriously as they are the rules of this forum.
http://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.
https://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    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 -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts cobol embedded sql MS_Developer COBOL Programming 7 Fri Oct 14, 2016 2:39 pm
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts Fault Analyser - missing code explana... egrove IBM Tools 1 Thu Aug 11, 2016 5:55 pm
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


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