1

I'm very new in using Cognos report studio and trying to filter some of the values and replace them into others.

I currently have values that are coming out as blanks and want to replace them as string "Property Claims"

what i'm trying to use in my main query is

CASE WHEN [Portfolio] is null then 'Property Claims' ELSE [Portfolio]

which is giving me an error. Also have a different filter i want to put in to replace windscreen flags to a string value rather than a number. For example if the flag is 1 i want to place it as 'Windscreen Claims'.

if [Claim Windscreen Flag] = 1 then ('Windscreen') Else [Claim Windscreen Flag]

None of this works with the same error....can someone give me a hand?

jiwon chang
  • 21
  • 2

2 Answers2

1

Your first CASE statement is missing the END. The error message should be pretty clear. But there is a simpler way to do that:

coalesce([Portfolio], 'Property Claims')

The second problem is similar: Your IF...THEN...ELSE statement is missing a bunch of parentheses. But after correcting that you may have problems with incompatible data types. You may need to cast the numbers to strings:

case
  when [Claim Windscreen Flag] = 1 then ('Windscreen')
  else cast([Claim Windscreen Flag], varchar(50))
end

In future, please include the error messages.

dougp
  • 2,810
  • 1
  • 8
  • 31
0

it might be syntax

  • IS NULL (instead of = null)
  • NULL is not blank. You might also want = ' '
  • case might need an else and END at the bottom
  • referring to a data type as something else can cause errors. For example a numeric like [Sales] = 'Jane Doe'

For example (assuming the result is a string and data item 2 is also a string),

case
when([data item 1] IS NULL)Then('X')
when([data item 1] = ' ')Then('X')
else([data item 2])
end

Also, if you want to show a data item as a different type, you can use CAST

VAI Jason
  • 534
  • 4
  • 14
  • 1
    Yeah, Cognos used to allow you to mix data types in a case statement, but that went away a few years ago, I think. – Andrew Apr 05 '21 at 19:57