[SQL0029] INTO clause missing from embedded statement.

895 pts.
Tags:
AS/400
DB2/400
iSeries
SQL
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


Software/Hardware used:
Db2/400
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    First, a SELECT statement isn't valid in the routine-body of a stored procedure. It's only valid as a select-clause of a DECLARE CURSOR statement, a sub-SELECT in another statement such as UPDATE or INSERT, or as some similar wrapper statement.

    So, second, if you want to use a "SELECT" kind of operation, you must use a SELECT INTO statement.

    I don't understand what is the need of INTO clause here.

    That's how SQL is. See the <select statement: single row> topic in the SQL standard. Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation).
    35,060 pointsBadges:
    report
  • HABBIE
    Thanks for the reply,
    I changed my function according to 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


    still same error:
    Run: SUS.REWT:(INT)

    [SQL0029] INTO clause missing from embedded statement.
    895 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: