Search code examples
sqlcognoscognos-bicognos-10

Incompatible data types in CASE STATEMENT

I am converting a SAS code into Cognos Report Studio version 10 where am stuck at a point where there is data formatting needed. 

IF SDW_STAT ^= '' THEN PRINCIPAL_BAL = TOT_PRIN;

Where '' represnts single quotes without any space

I tried writing a CASE STATEMENT:

CASE 
WHEN ([SDW_STAT] IS NOT MISSING) THEN ([PRINCIPAL_BAL] = [TOT_PRIN])
ELSE ('') 
END

I also tried couple of options like <> '', is not null, <> ' ' instead of IS NOT MISSING but none of them worked. Can you please suggest where I am going wrong? Pls note: SDW_STAT column has few blank fields and some characters like 'Y' 'S' etc.

Solution

  • Try putting the column outside of the CASE statement:

    PRINCIPAL_BAL = 
    CASE 
        WHEN COALESCE(SDW_STAT,'') <> '' THEN TOT_PRIN 
        ELSE PRINCIPAL_BAL 
    END

    Logically, this will update the principal_bal to tot_prin when sdw_stat doesn't equal blank or null. If it is blank, then it will just update it to itself.