case statement is not working after multiple conditions else ('')

[krishdw85]

Hi Team,

We are using cognos 10 version, In one of the dataitem i created some case statement...
case condition then 'x' else '' end ---- working

case condition1 then 'x' condition2 then 'x' condition3 then 'x' else '' end ---- not working.

Can someone guide me, not sure what i am missing here.....

TIA Team.
Regards,
Krish

[MFGF]

Hi Team,

We are using cognos 10 version, In one of the dataitem i created some case statement...
case condition then 'x' else '' end ---- working

case condition1 then 'x' condition2 then 'x' condition3 then 'x' else '' end ---- not working.

Can someone guide me, not sure what i am missing here.....

TIA Team.
Regards,
Krish

Hi,

Can you provide details on what you mean by "not working"? Do you get an error (and if so, what error)? Do you get an incorrect result (and if so, what is the result and what do you expect to get)? Do you get no result? Something else?

Can you also detail what the conditions are?

Cheers!

MF.

[krishdw85]

I mean when the condition is not met it has to display else'' that is blank. But it is somehow showing data (Incorrect result). Below is an example. Thanks for your quick response.

case
when jan then 'Y'
when feb then 'Y'
when mar then 'Y'
else '' end

For April also i see data as Y, generally it should be blank.

[MFGF]

I mean when the condition is not met it has to display else'' that is blank. But it is somehow showing data (Incorrect result). Below is an example. Thanks for your quick response.

case
when jan then 'Y'
when feb then 'Y'
when mar then 'Y'
else '' end

For April also i see data as Y, generally it should be blank.

Hi,

That looks to be an incomplete simple case statement? For this type of Case statement, you need to focus on an item, eg

case [Month]
when 'Jan' then 'Y'
when 'Feb' then 'Y'
when 'Mar' then 'Y'
else ''
end

The alternative complex Case statement would be

case
when [Month] = 'Jan' then 'Y'
when [Month] = 'Feb' then 'Y'
when [Month] = 'Mar' then 'Y'
else ''
end

Your expression needs to conform to one of these patterns.

Cheers!

MF.

[krishdw85]

simple case statement, still issue....

case
when [P] ='GB' and 
[EM] starts with 'M50M'
then 'Y'

when [P] ='SA' and  [Z] ='ACC' or
[PR] starts with 'POT'
then 'Y'

when [P] ='CH'
and [Z] = 'ACC'
or [PR] starts with  'E1647'

or [PR] starts with 'E190'
then 'Y'

when [P] ='VT'
And [Z] = 'ACC'
or [PR] starts with 'POT'
or [PR] starts with 'ASS'
then 'Y'

when  [P]= 'TR'
And [PR] ='A043'
Or [PR] = 'AS44'

Or [PR] starts with 'E19010' 
Or [PR] starts with 'ASS'
then 'Y'
 
when [P]='BR'
and [PR] starts with 'ASS'
then 'Y'

when [P]='CI'
and [PR] ='ASS08' or
 [PR]='ASS0' or
[PR]='ASS03'
then 'Y' 

when [P]='UA'
and [PR] starts with 'ASS'
then 'Y'
 
else ' '
end

[MFGF]

simple case statement, still issue....

case
when [P] ='GB' and 
[EM] starts with 'M50M'
then 'Y'

when [P] ='SA' and [Z] ='ACC' or
[PR] starts with 'POT'
then 'Y'

when [P] ='CH'
and [Z] = 'ACC'
or [PR] starts with  'E1647'
or [PR] starts with 'E190'
then 'Y'

when [P] ='VT'
And [Z] = 'ACC'
or [PR] starts with 'POT'
or [PR] starts with 'ASS'
then 'Y'

when  [P]= 'TR'
And [PR] ='A043'
Or [PR] = 'AS44'
Or [PR] starts with 'E19010' 
Or [PR] starts with 'ASS'
then 'Y'
 
when [P]='BR'
and [PR] starts with 'ASS'
then 'Y'

when [P]='CI'
and [PR] ='ASS08' or
 [PR]='ASS0' or
[PR]='ASS03'
then 'Y' 

when [P]='UA'
and [PR] starts with 'ASS'
then 'Y'
 
else ' '
end

Where you have conditions that use both AND and OR, the AND usually takes precedence. So for example

when [P] ='CH'
and [Z] = 'ACC'
or [PR] starts with  'E1647'
or [PR] starts with 'E190'
then 'Y'

would be evaluated as
([P] = 'CH' and [Z] = 'ACC') or ([PR] starts with  'E1647') or ([PR] starts with 'E190')

This would then return a TRUE result for any of the groups in parentheses - so any row where [PR] starts with 'E1647' (for example) would return a Y result from the case statement.

My guess is that you want this only to happen when [P] = 'CH' in this instance?

You should include your own parentheses to dictate how you want the conditions to be evaluated here.

You might end up with something like this:

case
when [P] ='GB' and 
[EM] starts with 'M50M'
then 'Y'

when [P] ='SA' and ([Z] ='ACC' or
[PR] starts with 'POT')
then 'Y'

when [P] ='CH'
and ([Z] = 'ACC'
or [PR] starts with  'E1647'
or [PR] starts with 'E190')
then 'Y'

when [P] ='VT'
And ([Z] = 'ACC'
or [PR] starts with 'POT'
or [PR] starts with 'ASS')
then 'Y'

when  [P]= 'TR'
And ([PR] ='A043'
Or [PR] = 'AS44'
Or [PR] starts with 'E19010' 
Or [PR] starts with 'ASS')
then 'Y'
 
when [P]='BR'
and [PR] starts with 'ASS'
then 'Y'

when [P]='CI'
and ([PR] ='ASS08' or
 [PR]='ASS0' or
[PR]='ASS03')
then 'Y' 

when [P]='UA'
and [PR] starts with 'ASS'
then 'Y'
 
else ' '
end

Cheers!

MF.

[krishdw85]

Thanks so much! Issue is fixed.

[MFGF]

Thanks so much! Issue is fixed.

Great news! Thanks for updating us!