CASE Statements

[LeonelSanchezJr]

I am trying to write a CASE statement which check 4 different fields for a value being less than a number.
When ALL 4 fields are each less than a number (24), then I set my case field to Y else N.

Here is my code

(CASE WHEN NVL(FIELD1,0) <= 24 THEN
CASE WHEN NVL(FIELD2,0) <= 24 THEN
CASE WHEN NVL(FIELD3,0) <= 24 THEN
CASE WHEN NVL(FIELD4,0) <= 24 THEN
'Y'
ELSE
'N'
END END END END) COMPLIANCE_MET


Thanks,

Leo ;-)

 

[feherke]

Hi

You forgot to mention your problem...

That way you have to set the [tt]else[/tt] value for each [tt]case[/tt], otherwise the first three will return [tt]null[/tt] if the condition evaluates to false.

(
  CASE
    WHEN NVL(FIELD1,0) <= 24 THEN
      CASE
        WHEN NVL(FIELD2,0) <= 24 THEN
          CASE
            WHEN NVL(FIELD3,0) <= 24 THEN
              CASE
                WHEN NVL(FIELD4,0) <= 24 THEN
                  'Y'
                ELSE
                  'N'
              END
            [red]else
              'N'[/red]
          END
        [red]else
          'N'[/red]
      END
    [red]else
      'N'[/red]
  END
) COMPLIANCE_MET

Would not be easier to use only one [tt]case[/tt] ?

(
  CASE
    WHEN NVL(FIELD1,0) <= 24 [red]and[/red] NVL(FIELD2,0) <= 24 [red]and[/red] NVL(FIELD3,0) <= 24 NVL(FIELD4,0) <= 24 [red]and[/red] THEN
      'Y'
    ELSE
      'N'
  END
) COMPLIANCE_MET

Feherke.

http://rootshell.be/~feherke/