[SOLVED] Convert SQL CASE Statement to If Then Else
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!
DanThis message has been edited. Last edited by: Dan Pinault,
7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
March 08, 2010, 06:01 PM
Dan Satchell
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);
WebFOCUS 7.7.05
March 09, 2010, 12:40 PM
njsden
How about this one? WebFOCUS syntax really helps to simplify those highly verbose CASE statements:
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,
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
7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.