Argh! I've been trying for the last few hours to convert the following SQL CASE statement into a form of If Then Else logic that WebFOCUS will accept!
Any ideas? Is there a rule-of-thumb to follow?

Here is the SQL code:

CASE WHEN (ENG_SOP < 1960) THEN
	CASE WHEN ((POP_YR - ENG_LIF_YRS) > 1960) THEN	
		CASE WHEN ((1996 - (POP_YR - ENG_LIF_YRS)) < 1.0)  THEN (1.0) 
		     WHEN ((1996 - (POP_YR - ENG_LIF_YRS)) >= 1.0) THEN (1996 - (POP_YR - ENG_LIF_YRS)) END
	     WHEN ((POP_YR - ENG_LIF_YRS) <= 1960) THEN (1996 - 1960) END
     WHEN (ENG_SOP >= 1960) THEN
	CASE WHEN ((POP_YR - ENG_LIF_YRS) > ENG_SOP) THEN 
		CASE WHEN ((1996 - (POP_YR - ENG_LIF_YRS)) < 1.0) THEN (1.0) 
		     WHEN ((1996 - (POP_YR - ENG_LIF_YRS)) >= 1.0) THEN (1996 - (POP_YR - ENG_LIF_YRS)) END
	     WHEN ((POP_YR - ENG_LIF_YRS) <= ENG_SOP) THEN
		CASE WHEN ((1996 - ENG_SOP) < 1.0) THEN (1.0) 
		     WHEN ((1996 - ENG_SOP) >= 1.0) THEN (1996 - ENG_SOP) END 
     ELSE 1.0 END END AS 'DAN'  

Thanks!

Dan

This message has been edited. Last edited by: Dan Pinault,

This is what I came up with.

VAL/D12.1 = IF (((POP_YR - ENG_LIF_YRS) GT 1960) OR ((POP_YR - ENG_LIF_YRS) GT ENG_SOP)) AND
                ((1996 - (POP_YR - ENG_LIF_YRS)) LT 1.0) THEN (1.0)                           ELSE
            IF (((POP_YR - ENG_LIF_YRS) GT 1960) OR ((POP_YR - ENG_LIF_YRS) GT ENG_SOP)) AND
                ((1996 - (POP_YR - ENG_LIF_YRS)) GE 1.0) THEN (1996 - (POP_YR - ENG_LIF_YRS)) ELSE
            IF ((ENG_SOP LT 1960) AND ((POP_YR - ENG_LIF_YRS) LE 1960)) THEN (1996 - 1960)    ELSE
            IF ((ENG_SOP GE 1960) AND ((POP_YR - ENG_LIF_YRS) LE ENG_SOP) AND
                ((1996 - ENG_SOP) LT 1.0)) THEN (1.0)                                         ELSE
            IF ((ENG_SOP GE 1960) AND ((POP_YR - ENG_LIF_YRS) LE ENG_SOP) AND
                ((1996 - ENG_SOP) GE 1.0)) THEN (1996 - ENG_SOP)                              ELSE (1.0);

How about this one? WebFOCUS syntax really helps to simplify those highly verbose CASE statements:

VAL/D12.1 = IF ENG_SOP LT 1960 
            THEN (IF (POP_YR - ENG_LIF_YRS) GT 1960    THEN MAX(1.0, 1996 - (POP_YR - ENG_LIF_YRS)) ELSE (1996 - 1960))
            ELSE (IF (POP_YR - ENG_LIF_YRS) GT ENG_SOP THEN MAX(1.0, 1996 - (POP_YR - ENG_LIF_YRS)) ELSE MAX(1.0, 1996 - ENG_SOP));

I didn't see a valid case where the final "ELSE 1.0" in the original code would apply as either (POP_YR - ENG_LIF_YRS) > ENG_SOP or (POP_YR - ENG_LIF_YRS) <= ENG_SOP would be met in any case, so I did not make a provision for that one.

Hope that helps,

- Neftali.

This message has been edited. Last edited by: njsden,

Here's a snippet of code using the CAR table testing the different 7 possible cases you'd encounter based in the original CASE statement:

DEFINE FILE CAR
RC/I2 WITH CAR = RC + 1;
ENG_SOP/I6     = DECODE RC (1 1955 2 1955 3 1955 4 1970 5 1970 6 1970 7 1999 ELSE 0); 
POP_YR/I6      = DECODE RC (1 2005 2 1990 3 1978 4 2010 5 1990 6 1975 7 1975 ELSE 0); 
ENG_LIF_YRS/I6 = DECODE RC (1 8    2 25   3 30   4 5    5 10   6 10   7 10   ELSE 0);
-*
VAL/D12.1      = IF ENG_SOP LT 1960 
                 THEN (IF (POP_YR - ENG_LIF_YRS) GT 1960    THEN MAX(1.0, 1996 - (POP_YR - ENG_LIF_YRS)) ELSE (1996 - 1960))
                 ELSE (IF (POP_YR - ENG_LIF_YRS) GT ENG_SOP THEN MAX(1.0, 1996 - (POP_YR - ENG_LIF_YRS)) ELSE MAX(1.0, 1996 - ENG_SOP));
END
-*
TABLE FILE CAR
PRINT 
      RC          AS 'Case'
      ENG_SOP
      POP_YR
      ENG_LIF_YRS
      VAL         AS 'Result'
WHERE RC LE 7
END

Regards,
- Neftali.

Thanks for the help Dan and Neftali!

I guess I couldn't see the forest for the trees as the saying goes. I was stuck on trying to convert the statement as it was written. The though or rearranging it never occured to me. Doh!

Regards!

Dan