CODEWITHSUNDEEP
db2db2-luw
Dilip Deenadayalan
Dilip Deenadayalan

Reputation: 17

Db2 stored procedure error (not valid in the context where it is used)

My issue is when i call the stored procedure it is not working 

drop procedure DELETE_WITH_COMMIT_COUNT1
DB20000I  The SQL command completed successfully.

CREATE PROCEDURE DELETE_WITH_COMMIT_COUNT1(IN v_TABLE_NAME VARCHAR(24), IN v_COMMIT_COUNT INTEGER )
    NOT DETERMINISTIC
    LANGUAGE SQL
P1 : BEGIN

    -- DECLARE Statements
    DECLARE SQLCODE INTEGER;
    DECLARE v_DELETE_QUERY VARCHAR(1024);
    DECLARE v_DELETE_STATEMENT STATEMENT;

P2 : BEGIN
    DECLARE V1 CHAR(24) FOR BIT DATA;
    DECLARE V2 CHAR(24) FOR BIT DATA ;

DECLARE cur1 CURSOR WITH RETURN TO CLIENT FOR select min(MESSAGE_ID),max(MESSAGE_ID) from TESTING where TIMESTAMP between (select TIMESTAMP(date(min(timestamp))) from TESTING with ur) and (select TIMESTAMP(date(min(timestamp))) + 1 day from TESTING with ur) ;

OPEN cur1;
FETCH FROM cur1 INTO V1, V2;

   SET v_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_TABLE_NAME || ' WHERE MESSAGE_ID between V1 and V2 '
        || ' FETCH FIRST ' || RTRIM(CHAR(v_COMMIT_COUNT)) || ' ROWS ONLY) AS DELETE_TABLE';

    PREPARE v_DELETE_STATEMENT FROM v_DELETE_QUERY;

    DEL_LOOP:
        LOOP
            EXECUTE v_DELETE_STATEMENT;
            IF SQLCODE = 100 THEN
                LEAVE DEL_LOOP;
            END IF;
            COMMIT;
        END LOOP;

    COMMIT;
  END P2;
END P1
DB20000I  The SQL command completed successfully.

My procedure was created successfully but when I call it the following issue happens:

db2 "call DELETE_WITH_COMMIT_COUNT1 ('TESTING',50)" SQL0206N "V1" is not valid in the context where it is used. SQLSTATE=42703

More information :

db2 "select min(MESSAGE_ID),max(MESSAGE_ID) from TESTING where TIMESTAMP between (select TIMESTAMP(date(min(timestamp))) from TESTING with ur) and (select TIMESTAMP(date(min(timestamp))) + 1 day from TESTING with ur) "

1                                                   2
--------------------------------------------------- ---------------------------------------------------
x'4B5753313032353039313133392020202020202020202020' x'4B5753313032353039313230302020202020202020202020'

  1 record(s) selected.

I want to delete the records between these values and i currently have 99 records between minimum and maximum message id 

message_id column is defined as CHAR(24) FOR BIT DATA on the table .

Upvotes: 1

Views: 2135

Answers (1)

Tucker Yates
Tucker Yates

Reputation: 13

Your problem is with this statement:

SET v_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_TABLE_NAME || ' WHERE   MESSAGE_ID between V1 and V2 '
        || ' FETCH FIRST ' || RTRIM(CHAR(v_COMMIT_COUNT)) || ' ROWS ONLY) AS DELETE_TABLE';

In this statement, it looks like you want to use the values of your previously declared variables, V1 and V2:

' WHERE   MESSAGE_ID between V1 and V2 '

DB2 sees this as a string literal. Instead, try changing this part of the statement like so:

SET v_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_TABLE_NAME || ' WHERE   MESSAGE_ID between ' || V1 || ' and ' || V2
            || ' FETCH FIRST ' || RTRIM(CHAR(v_COMMIT_COUNT)) || ' ROWS ONLY) AS DELETE_TABLE';

Upvotes: 0

Related Questions