I have the following inline SQL code, running on an AS400. The IF comes up as unexpected but I don’t understand why. What I’m trying to do is to build a stored procedure to update, or insert, a table row. The code shown below is inline to ensure my logic is correct.
I’ve tried a number of different approaches including IF EXISTS, CASE, SET @COUNT = SELECT COUNT(*). I continue to run into odd errors.
In the code shown below, I fail when I hit the IF statement.
CREATE OR REPLACE VARIABLE IN_LOC CHAR(02); CREATE OR REPLACE VARIABLE IN_MCO CHAR(02); CREATE OR REPLACE VARIABLE IN_SYMBOL CHAR(03); CREATE OR REPLACE VARIABLE IN_POLICYNUM CHAR(07); CREATE OR REPLACE VARIABLE IN_MODULE CHAR(02); CREATE OR REPLACE VARIABLE IN_RISKLOC CHAR(05); CREATE OR REPLACE VARIABLE IN_PRCCLASS VARCHAR(05); CREATE OR REPLACE VARIABLE IN_PRCTER VARCHAR(03); CREATE OR REPLACE VARIABLE IN_MESSAGE CHAR(02); SET IN_LOC='00'; SET IN_MCO='01'; SET IN_SYMBOL='CPP'; SET IN_POLICYNUM='0003748'; SET IN_MODULE='00'; SET IN_RISKLOC='00003'; SET IN_PRCCLASS='09'; SET IN_PRCTER='1'; SET IN_MESSAGE='NOT FOUND FOR ODD PROGRAMMING'; UPDATE BASPPRC01 SET PRCCLASS=IN_PRCCLASS,PRCTER=IN_PRCTER,MESSAGE=IN_MESSAGE WHERE CAST(IN_LOC||IN_MCO||IN_SYMBOL||IN_POLICYNUM||IN_MODULE||IN_RISKLOC AS CHAR(21)) = CAST(LOCATION||MASTERCO||SYMBOL||POLICYNUM||MODULE||RISKLOC AS CHAR(21)) ; IF @@ROWCOUNT = 0 BEGIN INSERT INTO BASPPRC01 (LOCATION,MASTERCO,SYMBOL,POLICYNUM,MODULE,RISKLOC,PRCCLASS,PRCTER,MESSAGE) VALUES (IN_LOC , IN_MCO , IN_SYMBOL , IN_POLICYNUM , IN_MODULE , IN_RISKLOC , IN_PRCCLASS , IN_PRCTER , IN_MESSAGE ) ; END COMMIT TRANSACTION ;
Advertisement
Answer
You should show the error you get…
with the following:
CREATE OR REPLACE PROCEDURE myproc () LANGUAGE SQL BEGIN UPDATE BASPPRC01 SET PRCCLASS=IN_PRCCLASS,PRCTER=IN_PRCTER,MESSAGE=IN_MESSAGE WHERE CAST(IN_LOC||IN_MCO||IN_SYMBOL||IN_POLICYNUM||IN_MODULE||IN_RISKLOC AS CHAR(21)) = CAST(LOCATION||MASTERCO||SYMBOL||POLICYNUM||MODULE||RISKLOC AS CHAR(21)) ; IF @@ROWCOUNT = 0 BEGIN INSERT INTO BASPPRC01 (LOCATION,MASTERCO,SYMBOL,POLICYNUM,MODULE,RISKLOC,PRCCLASS,PRCTER,MESSAGE) VALUES (IN_LOC , IN_MCO , IN_SYMBOL , IN_POLICYNUM , IN_MODULE , IN_RISKLOC , IN_PRCCLASS , IN_PRCTER , IN_MESSAGE ) ; END COMMIT TRANSACTION ; END;
I get [SQL0199] Keyword BEGIN not expected. Valid tokens: THEN.
This passes a syntax check, note THEN
and END IF
instead of BEGIN
also, COMMIT
instead of COMMIT TRANSACTION
CREATE OR REPLACE PROCEDURE procedure2 () LANGUAGE SQL BEGIN UPDATE BASPPRC01 SET PRCCLASS=IN_PRCCLASS,PRCTER=IN_PRCTER,MESSAGE=IN_MESSAGE WHERE CAST(IN_LOC||IN_MCO||IN_SYMBOL||IN_POLICYNUM||IN_MODULE||IN_RISKLOC AS CHAR(21)) = CAST(LOCATION||MASTERCO||SYMBOL||POLICYNUM||MODULE||RISKLOC AS CHAR(21)) ; IF @@ROWCOUNT = 0 THEN INSERT INTO BASPPRC01 (LOCATION,MASTERCO,SYMBOL,POLICYNUM,MODULE,RISKLOC,PRCCLASS,PRCTER,MESSAGE) VALUES (IN_LOC , IN_MCO , IN_SYMBOL , IN_POLICYNUM , IN_MODULE , IN_RISKLOC , IN_PRCCLASS , IN_PRCTER , IN_MESSAGE ) ; END IF; COMMIT; END;