[SQL0029] INTO clause missing from embedded statement.

HABBIE · New User Joined: 08 Jul 2014 Posts: 24 Location: INDIA

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

Nic Clouston · Posted: Fri Sep 04, 2015 4:48 pm

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.

Rohit Umarjikar · Posted: Fri Sep 04, 2015 9:05 pm

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