Does anyone know how to code the following SQL statement into WebFocus code? Thanks.

SUM (CASE WHEN field1 IS NULL                 
AND field2 IS NULL
AND nvl(field3,'X') <> 'WXYZ'
AND (field4amt > 0
OR field5 = 'MANU'
OR (field6 = 'Y'
AND (nvl(field5,'X') <> 'BRID'
OR field7 IS NULL )))
THEN 1 ELSE 0 END ) miscellaneous,

This message has been edited. Last edited by: Michele Brooks,

Hi Michele,

Oracle, correct?

The NVL function assigns a value to a test if the donor field is null - a bit like coalesce.

So the following code should work -

SUM COMPUTE W_field3/A4 = IF field3 IS MISSING THEN 'X' ELSE field3; NOPRINT
    COMPUTE W_field5/A4 = IF field5 IS MISSING THEN 'X' ELSE field5; NOPRINT
    COMPUTE miscellaneous/I1 = IF field1 IS MISSING
                              AND field2 IS MISSING
                              AND W_field3 NE 'WXYZ'
                              AND (field4amt > 0
                                OR field5 EQ 'MANU'
                                OR (field6 EQ 'Y' AND (W_field5 NE 'BRID' OR field7 IS MISSING)))
                         THEN 1 ELSE 0;

Not tested obviously but should give you something to go on.


T

Thank you so much, but unfortunately I still do not get the desired results. I was just given some alternate logic to use; therefore I am closing this case. Thanks again.