I am working with 2 tables that are related through a left outer join. I am looking for the records in the first table where the join turned up a null value(the records did not exist in the second table). I tried a where is missing, where EQ ‘’, and where EQ ’ ‘ in the second table and every time it came up with 0 records. Does anyone know of another way to get records where the right table has no records?
e.g. Original Table ID C1 C2 1 record-1 data-1 2 record-2 data-2 3 record-3 data-3 4 record-4 data-4 5 record-5 data-5
Joined table ID C1 C2 C3 1 Y record-A other-A 3 N record-C other-C 5 Y record-E other-E
JOIN LEFT_OUTER Original_Table.ID IN Original_Table TO UNIQUE Joined_Table.ID IN Joined_Table TAG J4 as J4 END
I want the report to produce all records in Original_Table that do not exist in Joined_Table. (2,4)This message has been edited. Last edited by: Todd_Wallace,
WebFOCUS 8.1.05 Windows-iSeries DB2, All Outputs HTML
November 11, 2011, 04:37 PM
Dan Satchell
Prior to your JOIN, try:
SET ALL = ON
or
SET ALL = SQL
or, if you have a WHERE clause on the child instance
SET ALL = PASS
WebFOCUS 7.7.05
November 13, 2011, 02:48 PM
Waz
quote:
SET ALL = SQL
When did this come in ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 14, 2011, 10:09 AM
Todd_Wallace
SET ALL = PASS
[/QUOTE]
That worked great I had a where clause to determine if it was blank to show it. Thanks a bunch, Todd
WebFOCUS 8.1.05 Windows-iSeries DB2, All Outputs HTML
November 21, 2011, 11:21 AM
ABT
quote:
Originally posted by Waz:
quote:
SET ALL = SQL
When did this come in ?
7.7.x Walter Blood did a demo of it in our WFUG last month.