## Need help undestanding unique CASE END WHEN THEN logic

Solved
Occasional Contributor
Posts: 12

# Need help undestanding unique CASE END WHEN THEN logic

I am currently porting report summary SQL from COGNOS Report Sudio to SAS EG. The SQL in the below segment defines a sub-total label for widgets in 2 different grouping categories. I know widget (type) is suppose to resolve to a range of either 1-11 or 13-28 based on its subtype, but I am baffled how this logic works once it finishes the inner most case statement.

Could someone help me understand the logic of the 5th line from the bottom  i.e. "end  < 1"  and how it pertains to the remaining 4 lines? I see the obvious logic but it does not make sense to me as applied to the result, specifically the "end  < 1".

Here is and abridged version of the SQL segment:

```case
when
case
when
(case
when (Widget.SubType in (100, 105, 110, 115)) then 1
when (Widget.SubType in (140, 145)) then 3
...........
...........
when (Widget.SubType in (133, 138)) then 11
when (Widget.SubType in (71, 72)) then 13
when (Widget.SubType in (80, 81, 85, 88)) then 14
...........
...........
when (Widget.SubType in (200, 201, 202, 205, 210, 215, 225, 230, 235)) then 28
else 999
end  < 1)
then '1' else '2'
end  = '1'
then 'Widget Types 1 through 11' else 'Widget Types 13 through 28'
end ```

Accepted Solutions
Solution
‎03-06-2017 02:53 PM
Super Contributor
Posts: 359

## Re: Need help undestanding unique CASE END WHEN THEN logic

Hello,

This is how I understand it :

The part

``````case
when (Widget.SubType in (100, 105, 110, 115)) then 1
when (Widget.SubType in (140, 145)) then 3
...........
...........
when (Widget.SubType in (133, 138)) then 11
when (Widget.SubType in (71, 72)) then 13
when (Widget.SubType in (80, 81, 85, 88)) then 14
...........
...........
when (Widget.SubType in (200, 201, 202, 205, 210, 215, 225, 230, 235)) then 28
else 999
end``````

computes a value based on SubType. If we call X this value, the next step si to evaluate :

``````case
when  X < 1
then '1' else '2'
end``````

So if the value reflecting the SubType is less than 1, we set a new value, say Y, to 1. Y=2 instead.

Now if Y=1, then we return the string 'Widegets Type 1 to 11' and 'Widgets Type 13 to 28' otherwise

Maybe there is a typo and the "<1" should be "<12" ?

All Replies
Super User
Posts: 5,919

## Re: Need help undestanding unique CASE END WHEN THEN logic

I haven't seen such logic in ANSI SQL, so my guess is that this is Cognos specific logic. If so, contact your Cognos resource for explanation.

Normally, each when should have a condition and I can't see such in the surrounding case-when's.

Is the first mapping used somewhere else?

If not, assign the 1-11 and so on directly and skip all the nestings.

Data never sleeps
Solution
‎03-06-2017 02:53 PM
Super Contributor
Posts: 359

## Re: Need help undestanding unique CASE END WHEN THEN logic

Hello,

This is how I understand it :

The part

``````case
when (Widget.SubType in (100, 105, 110, 115)) then 1
when (Widget.SubType in (140, 145)) then 3
...........
...........
when (Widget.SubType in (133, 138)) then 11
when (Widget.SubType in (71, 72)) then 13
when (Widget.SubType in (80, 81, 85, 88)) then 14
...........
...........
when (Widget.SubType in (200, 201, 202, 205, 210, 215, 225, 230, 235)) then 28
else 999
end``````

computes a value based on SubType. If we call X this value, the next step si to evaluate :

``````case
when  X < 1
then '1' else '2'
end``````

So if the value reflecting the SubType is less than 1, we set a new value, say Y, to 1. Y=2 instead.

Now if Y=1, then we return the string 'Widegets Type 1 to 11' and 'Widgets Type 13 to 28' otherwise

Maybe there is a typo and the "<1" should be "<12" ?

Occasional Contributor
Posts: 12

## Re: Need help undestanding unique CASE END WHEN THEN logic

Maybe there is a typo and the "<1" should be "<12" ?

That would make perfect sense with the logic as I see it ... I will ponder that as well and see if I can validate.

Super Contributor
Posts: 359

## Re: Need help undestanding unique CASE END WHEN THEN logic

The query seems overly complicated though as intermediary calculations are lost.

``````DATA have;
x=110; output;
x=202; output;
x=140; output;
run;

proc sql noprint;
CREATE TABLE want AS
SELECT case when
case when
(case
when (x in (100, 105, 110, 115)) then 1
when (x in (140, 145)) then 3
when (x in (133, 138)) then 11
when (x in (71, 72)) then 13
when (x in (80, 81, 85, 88)) then 14
when (x in (200, 201, 202, 205, 210, 215, 225, 230, 235)) then 28
else 999
end  < 12) then '1' else '2'
end  = '1'
then 'Widget Types 1 through 11' else 'Widget Types 13 through 28'
end AS TYPE format=\$50.
FROM have;

CREATE TABLE want2 AS
SELECT case when x in (100, 105, 110, 115, 140, 145, 133, 138) then 'Widget Types 1 through 11'
else 'Widget Types 13 through 28'
end as TYPE format=\$50.
FROM have;
quit;``````
☑ This topic is solved.