I have multiple parameters, but one of them has null values. Tried to create a SET with the WHERE statement.
Is there a way to do this so that it captures the null values?
-SET &DEC = IF &DEC NE 'Y' OR &DEC EQ MISSING THEN 'FOC_NONE' ELSE &DEC;
WHERE J3.SEG01.DECEASED EQ &DEC.(OR (, )).Deceased.;
thanks.This message has been edited. Last edited by: DG,
WebFOCUS 7.702 Windows, All Outputs
April 11, 2012, 05:37 PM
Waz
I think you need to find out what is passed to the amper variable when its NULL, so you can code your test correctly.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
April 12, 2012, 01:19 AM
Dan Satchell
If the values are truly null, it seems to me that the variables would contain the null value ".". If so, something like this might work.
-SET &DEC = IF (&DEC NE 'Y') OR (&DEC EQ '.') THEN 'FOC_NONE' ELSE &DEC ;
WebFOCUS 7.7.05
April 12, 2012, 02:58 PM
DG
That recommendation sort of worked.
Not getting all records because even though I've denoted UNIQUE LEFT OUTER JOINs, TABLE_THREE does not have the same count of unique records as the other tables. It only includes records that have a certain field populated.
BTW, I have my joins in "serial" order instead of "star" to expedite run times.
Is there a way around this?
JOIN LEFT_OUTER TABLE_ONE.TABLE_ONE.FIELD_1 IN TABLE_ONE TO UNIQUE TABLE_TWO.TABLE_TWO.FIELD_1 IN TABLE_TWO TAG J0 AS J0 END JOIN LEFT_OUTER J0.TABLE_TWO.FIELD_1 IN TABLE_ONE TO UNIQUE TABLE_THREE.TABLE_THREE.FIELD_2 IN TABLE_THREE TAG J8 AS J8 END JOIN LEFT_OUTER J8.TABLE_THREE.FIELD_2 IN TABLE_ONE TO UNIQUE TABLE_FOUR.TABLE_FOUR.FIELD_1 IN TABLE_FOUR TAG J1 AS J1 END JOIN LEFT_OUTER J1.TABLE_FOUR.FIELD_1 IN TABLE_ONE TO UNIQUE ADR_HOLD.SEG01.FIELD_2 IN ADR_HOLD TAG J3 AS J3 END
WebFOCUS 7.702 Windows, All Outputs
April 12, 2012, 03:10 PM
Dan Satchell
If these are FOCUS tables, try setting ALL=ON, or ALL=PASS if you have WHERE clauses on any of the JOIN TO tables. Otherwise, can you take TABLE_THREE out of the serial JOIN sequence and make it a "star" join?
WebFOCUS 7.7.05
April 16, 2012, 12:56 PM
DG
I will try both suggestions and see which one runs faster.