Search code examples
sqlif-statementdb2alter

Set table column with IF CLAUSE or CASE WHEN

I am trying to create a calculated field within my table in SQL for DB2. I altered table adding new field.

alter table edi.base_completeness add AF_flg int

I need to set this column with calculated value based on other data in the record. Specifically, the following.

IF ( [Ship Evnt Cd] is null ) THEN
( 0 )
ELSE IF ( [Ship Evnt Cd] = 'AF' ) THEN 
    ( 1 )
ELSE
    ( 0 )

Furthermore, after this flag is ok (and defining other 2 ones, D_flg and X1_flg) I need another calculated field using these ones.

alter table edi.base_completeness add test_missing_event varchar(30)

And set with this logic.

IF ( total ([AF_flg] for [Ship_Id], [Carr_Cd]) <> 0 and total ([D_flg] for
[Ship_Id],  [Carr_Cd]) = 0 OR [X1_flg] for [Ship_Id],  [Carr_Cd]) = 0  ) THEN
    (  'Missing Delivery' )
ELSE IF ( total ([AF_flg] for [Ship_Id], [Carr_Cd]) = 0 and total ([D_flg]
 for [Ship_Id],  [Carr_Cd]) <> 0 OR [X1_flg] for [Ship_Id],  [Carr_Cd]) <> 0) THEN
    (  'Missing Pickup' )
ELSE IF ( total ([AF_flg] for [Ship_Id], [Carr_Cd]) = 0 and total ([D_flg]
 for [Ship_Id],  [Carr_Cd]) = 0 OR [X1_flg] for [Ship_Id],  [Carr_Cd]) = 0 ) THEN
    ( 'Missing Both' )
ELSE
    ( 'Not Missed' )

I am trying to figure out the code using UPDATE ... SET and then including IF or CASE clauses instead of simple = but I get error, expecting creation statement instead of suppossed code.

UPDATE EDI.BASE_COMPLETENESS SET AF_FLG CASE WHEN SHIP_EVNT_CD IS NULL THEN 0 ELSE 1 END;

"create_variable" expected instead of this input

(I hope the CASE clause is finely structured and not the cause of the error, but the data is sample just to test, not following statement below)

Is this approach to update the calculated column correct? Should it be done with a trigger instead?

Thanks!

Solution

  • What version of Db2 are you using? In Db2 for LUW, your statments gives the following error

    SQL0104N An unexpected token "CASE" was found following "PLETENESS SET AF_FLG". Expected tokens may include: "=". SQLSTATE=42601

    Adding the missing = should solve your problem.

    UPDATE EDI.BASE_COMPLETENESS SET AF_FLG = CASE WHEN SHIP_EVNT_CD IS NULL THEN 0 ELSE 1 END