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 advance

This message has been edited. Last edited by: Kerry,

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...

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.

Thank you, Ginny,

I'll take your thoughts and put them into my code, and see what pops up. I appreciate your replying.

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?

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.

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.

Check this out to learn how to do a left outer join...

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.