[CLOSED] I need to include data for missing records
I have two tables that I am joining together. In the cse of one table not having related data in comparison to the other table, I want to be able to have the missing field contain something like "No data".
I have search this forum and the web but have not located what I think I need. Here is pseudo code for what I am trying:
SET MISSING = NO DATA JOIN FIELDA in TABLEA to FIELDA in TABLEB TABLE FILE TABLEA PRINT FIELDA END -RUN
Is this the correct way of doing this? If not, would someone please help to point out what I am not doing? The reason I ask is that I am not getting successful results. I want to have my TABLEA still show its data, whether there is a match to data in TABLEB or not, but that is not happening.
Thank you in advanceThis message has been edited. Last edited by: Kerry,
Mainframe FOCUS 7.0 VM/CMS and MVS/TSO
June 25, 2009, 02:11 PM
GamP
If you have a 1-1 join, like you have posted, you'll never see the missing data, since missing data in that case will be represented as spaces or 0, depending on the format of the field. If you want missing data to show, then you'll have to have a one to many join (JOIN ... TO ALL ...) and you'll have to have SET ALL=ON or SET ALL=PASS (depending on your request) to see the missing data. If you want it to show up as 'NO DATA' in your report, issue SET NODATA='NO DATA'
Hope this helps...
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
June 25, 2009, 02:13 PM
GinnyJakes
Look up left outer joins, SET ALL=ON, and using MISSING in DEFINES. You can test the field in the target table for MISSING, then if true, set the value of the new field to 'NO DATA' assuming that the format of the field suports that.
I'll take your thoughts and put them into my code, and see what pops up. I appreciate your replying.
Mainframe FOCUS 7.0 VM/CMS and MVS/TSO
June 26, 2009, 12:01 PM
webmeister
My program is still not showing me anything for the missing fields, where I want it to show 'NO DATA' Here is the piece of code that is causing my problem:
TABLE FILE SAFILE
PRINT
SA2C4 AS BIGEFC
NEFC AS EFC
BY STU_ID_3 AS STU_ID
ON TABLE HOLD AS R187Y&INSTX
END
-RUN
-*
SET NODATA = 'NO DATA'
-*
JOIN CLEAR *
JOIN STU_ID IN R187X&INSTX TO ALL STU_ID IN R187Y&INSTX AS FILE001
-*
DEFINE FILE R187X&INSTX
NEWEFC /A05 = IF EFC EQ MISSING THEN 'NONE' ELSE EFC;
END
-*
TABLE FILE R187X&INSTX
-*
PRINT
-*
TERM
MAX.DUALSTD
MAX.RT134 AS RT134
MAX.DUALTERMS
AA642 AS AA642
RB110 AS 'RB110'
RB205 AS 'RB205'
MAX.SCORE1
MAX.SCORE2
MAX.SCORE3
MAX.SCORE4
MAX.SCORE5
MAX.SCORE6
MAX.SCORE7
MAX.SCORE8
MAX.TEST_DT
TEST_CTR
AA003 AS AA003
INFORLSE
NEWEFC
BIGEFC
-**** AWD_YR
-*
BY STU_ID
-*
-*WHERE RB205 EQ 'ACCUP'
WHERE RB110 EQ '&HS1' OR '&HS2' OR '&HS3'
OR '&HS4' OR '&HS5' OR '&HS6'
OR '&HS7'
-*WHERE INFORLSE EQ 'Y'
-*WHERE TERM EQ '&TERM'
-*OR DUALSTD EQ 'Y'
-*WHERE AA642 EQ 'HS' OR 'EA'
-*
ON TABLE HOLD AS R187D&INSTX
-*
END
-RUN
-*
What am I doing that is wrong?
Mainframe FOCUS 7.0 VM/CMS and MVS/TSO
June 26, 2009, 12:25 PM
DW Marker
You are still doing an inner join, this has to be done as a left outer join or you won't ever see the missing ones.
WF 7.6.4 Windows XP and UNIX
June 26, 2009, 12:29 PM
webmeister
Thanks for replying, DW Marker, but how would I change this to a left outer join? I am working in Mainframe FOCUS Rel 7.1 and am not sure if I can specify this as a left outer join.
Mainframe FOCUS 7.0 VM/CMS and MVS/TSO
June 26, 2009, 12:39 PM
Doug
Check this out to learn how to do a left outer join...
In FOCUS Since 1983 ~ from FOCUS to WebFOCUS. Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
June 26, 2009, 01:37 PM
Darin Lee
Also, when you create your first hold file you must set HOLDMISS=ON or you do not get missing values in your hold file - they are blank instead. From the manual:
quote:
Parameter: HOLDMISS
Description: Enables you to distinguish between missing data and default values of blank (for character data) or zero (for numeric data) in a HOLD file.
Syntax: SET HOLDMISS = {OFF|ON}where:
OFF
Does not allow you to store missing data in a HOLD file. OFF is the default value.
ON
Enables you to store missing data in a HOLD file. When TABLE generates a default value for data not found, it generates missing values.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat