robert fuschetto Posted September 30, 2022 Share Posted September 30, 2022 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:JOINLEFT_OUTER HOLD_VISITWRVUS_PRELIM4.HOLD_VIS.NPIALPHA IN HOLD_VISITWRVUS_PRELIM4TO MULTIPLE HOLDNEWHIRES.SEG01.NPIALPHA IN HOLDNEWHIRES TAG J11 AS J11END: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? Link to comment Share on other sites More sharing options...
robert fuschetto Posted September 30, 2022 Author Share Posted September 30, 2022 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. Link to comment Share on other sites More sharing options...
robert fuschetto Posted September 30, 2022 Author Share Posted September 30, 2022 nor did this work..it must have something to do with the join..I wonder if I need another hold file...ughNEWHIRETMP/A1=IF J11.SEG01.NEWHIREFLAG EQ MISSING THEN 'N' ELSE 'Y';NEWHIRE/A1=IF J11.SEG01.NEWHIRETMP EQ 'Y' THEN 'Y' ELSE 'N'; Link to comment Share on other sites More sharing options...
robert fuschetto Posted September 30, 2022 Author Share Posted September 30, 2022 I solved this by creating an extra hold file then defining the flag off the value therein. Inefficient but it works. Link to comment Share on other sites More sharing options...
Martin Yergeau Posted October 3, 2022 Share Posted October 3, 2022 Don't know everything about your environment, but may be related to your connextion string and/or the settingsBelow two SET's may make a difference depending on selected option :SET ALL = OFF/ON/PASSSET SHORTPATH = FOCUS/SQL Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now