CODEWITHSUNDEEP
sqlcognoscognos-bicognos-10
Excited_to_learn
Excited_to_learn

Reputation: 359

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.

Upvotes: 0

Views: 2959

Answers (2)

Johnsonium
Johnsonium

Reputation: 2005

The problem is likely the ELSE statement. Your THEN clause is an equation. This will return a boolean, either true or false. Your else is returning a string. These types aren't compatible. You can test this by changing your ELSE to:

ELSE (false)

My guess is the data type error will go away.

Can you elaborate on where you are using this code? Is it in a data item or in a filter? There are different rules depending on where your expression is used.

Note: I would comment on the question directly but I'm new and don't yet have the ability.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

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.

Upvotes: 3

Related Questions