Need help undestanding unique CASE END WHEN THEN logic

[eg_michael]

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 

[LinusH]

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.

[gamotte · Accepted solution]

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" ?

[eg_michael]

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.

[gamotte]

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;