HELP.....NULL TEST?

[robert fuschetto]

I have this join which takes my big table with 1000's of records an links to an xref with 100 records - note it is a LEFT outer join:

JOIN

LEFT_OUTER HOLD_VISITWRVUS_PRELIM4.HOLD_VIS.NPIALPHA IN HOLD_VISITWRVUS_PRELIM4

TO MULTIPLE HOLDNEWHIRES.SEG01.NPIALPHA IN HOLDNEWHIRES TAG J11 AS J11

END:

I then have this define:

NEWHIRE/A1=IF J11.SEG01.NEWHIREFLAG EQ 'Y' THEN 'Y' ELSE 'N';

Now at this point I think what is happening is that NEWHIRE has a value of 'Y' for the 100 records where there is a hit. I SUSPECT that where there is not hit, NEWHIRE is NULL......not 'N' as I had hoped.

Do you concur?

I suspect this because when I report NEWHIRE I ONLY see values of 'Y' and NULL...no 'N's.

The trouble is I need the records where there is no match to carry and 'N' not a NULL.

It feels like I need my NEWHIRE definition to say

IF J11.SEG01.NEWHIREFLAG EQ 'Y' THEN 'Y' ELSE IF THERE WAS NOT A MATCH THEN 'N".

How could I code do this?

[robert fuschetto]

this also did not work:

NEWHIRE/A1=IF J11.SEG01.NEWHIREFLAG EQ MISSING THEN 'N' ELSE 'Y';

Whenever I report   J11.SEG01.NEWHIRE I get values of Y and NULL instead of Y and N.

[robert fuschetto]

nor did this work..it must have something to do with the join..I wonder if I need another hold file...ugh

NEWHIRETMP/A1=IF J11.SEG01.NEWHIREFLAG EQ MISSING THEN 'N' ELSE 'Y';

NEWHIRE/A1=IF J11.SEG01.NEWHIRETMP EQ 'Y' THEN 'Y' ELSE 'N';

[robert fuschetto]

I solved this by creating an extra hold file then defining the flag off the value therein. Inefficient but it works.

[Martin Yergeau]

Don't know everything about your environment, but may be related to your connextion string and/or the settings

Below two SET's may make a difference depending on selected option :

SET ALL = OFF/ON/PASS

SET SHORTPATH = FOCUS/SQL