sql - Keyword IF not expected

Question

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 ;

question from:https://stackoverflow.com/questions/65849927/keyword-if-not-expected

Answer 1

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;