Focal Point
[SOLVED]Parameters with Null Values

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6457088816

April 11, 2012, 02:53 PM
DG
[SOLVED]Parameters with Null Values
Hi,

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.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs: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.


WebFOCUS 7.702
Windows, All Outputs
April 18, 2012, 04:23 PM
DG
STAR join fixed the problem.


WebFOCUS 7.702
Windows, All Outputs