Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] I need to include data for missing records

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] I need to include data for missing records
 Login/Join
 
Guru
posted
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,


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Guru
posted Hide Post
Thank you, Ginny,

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
 
Posts: 250 | Registered: January 14, 2008Report This Post
Guru
posted Hide Post
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
 
Posts: 250 | Registered: January 14, 2008Report This Post
Silver Member
posted Hide Post
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
 
Posts: 41 | Registered: November 26, 2008Report This Post
Guru
posted Hide Post
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
 
Posts: 250 | Registered: January 14, 2008Report This Post
Expert
posted Hide Post
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
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] I need to include data for missing records

Copyright © 1996-2020 Information Builders