I am trying to join two tables together, one table that is called Detail_Aging table and the other one is called Invoice_Notes table. The only field that they both have in common is Transaction_number. When I join the two tables I have to filter out all the transaction_numbers that have an invoice_note. This means that my report should only display the transaction_numbers that are not in the Invoice_Notes table.

The only problem that I have is that the transaction_numbers in the detail_aging table contain "All" the transaction_numbers and the transaction_number in the Invoice_notes table only contain the ones that have invoice notes attach to them. So when I join the tables together by the transaction_number and then filter the detailed_aging.transaction_number NE Invoice_Notes.transaction_number I do not get any results back.

I looked through the message boards and someone had a similar problem and they were suggested to use MATCH but I cannot figure out how it works (I looked through the documentation) and I am not sure if that will work in my case.

Thanks!

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

BJ,

A couple more pieces of information would be useful here, i.e. what kind of tables are they, DB2, SQL Server, etc.; how many rows are there in each table; can there be multiple notes transctions in the notes table for each transaction number in the aging table?

As for MATCH, which you wouldn't necessarily want to use if there are huge numbers of rows in each table, you would do something like this:

MATCH FILE DETAIL_AGING
PRINT FLD1 FLD2 etc.
BY TRANSNO
RUN 
FILE INVOICE_NOTES
BY TRANSNO
AFTER MATCH HOLD OLD-NOT-NEW
END

Does that help?

With the JOIN try:

SET ALL = ON
-*
JOIN TRANSACTION_NUMBER IN INVOICE_NOTES TO ALL TRANSACTION_NUMBER IN DETAILED_AGING AS J1
-*
TABLE FILE INVOICE_NOTES
 PRINT TRANSACTION_NUMBER
 WHERE DETAILED_AGING.TRANSACTION_NUMBER IS MISSING ;
END

They are DB2 tables and there can be multiple note transaction numbers in the notes table for each transaction number in the aging table.

By rows do you mean how many rows do the note transaction numbers and the aging transaction numbers contain?


Dan- I tried what you posted and it returned an empty report. I have tried something similar to that earlier as well with the IS MISSING but it did not work.

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

If the relationship between NOTES and AGING is many-to-one, the "ALL" in the JOIN statement is not needed. But the SET ALL=ON is necessary. Try filtering on another column in the AGING table instead of TRANSACTION_NUMBER with "IS MISSING".

EDIT: One more thing to try with the JOIN is something I learned from Francis Mariani today: SET ALL = SQL.

This message has been edited. Last edited by: Dan Satchell,

quote:

By rows do you mean how many rows do the note transaction numbers and the aging transaction numbers contain?

I mean how many rows there are in each table.

Relative to Dan's solution, I have never been able to get that to work. However, if you did the SET ALL=ON or left outer join and held the results then do the test on missing from the hold file, you wouldn't have to do a match. Just select the columns you will need from the aging table for your report.

There are 154 rows in the aging table and 15 rows in the notes table.

How would I hold the results from a join and then test the hold file?

Im sorry I am still new to WebFocus.

I believe you got no result because of the WHERE statement turns your left-outer join to inner join, which gives you all the matching transaction numbers, but you tell the program to report inv_transction# <> note_transaction# therefore nothing satisfy that condition.

I have so much trouble with the left-outer join, one of the best advice I got from the forum to join files first, hold the result, then filter out any note_transaction# > 0, (not very efficient, but works). Of course you also need SET ALL=ON etc as many posts suggested.

I forbid myself working with missing columns in DEFINE, COMPUTE or WHERE, until I hold the result first.

(I have repeated what Jinny said )
Hua

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

Hua, v7.7 promises to fix the problem with a LEFT OUTER JOIN and a filter on missing data:

[CLOSED] Outer Join on only one of the Joins

Thanks Francis.

I guess I am lack of confidence. I will try it when time comes.

We just upgraded to 7.6.11. Haven't have the courage to declare it in my profile.

Existing PDF outputs are distorded by the enhancement. Incorrect calendar dates returned to fex...

Hua

SET HOLDMISS=ON
SET ALL=ON
JOIN TRANSNO IN AGING TO TRANSNO IN NOTES AS J1
TABLE FILE AGING
PRINT fields_you_need_from_aging_table 
some_fields_from_notes_table
BY TRANSNO
ON TABLE HOLD FORMAT ALPHA
END
TABLE FILE HOLD
PRINT fields_you_need_from_aging_table 
WHERE some_field_from_notes_table IS MISSING
{ON TABLE HOLD AS HOLD2 FORMAT ALPHA}
END

This is pseudo-code and you'll have to adjust to meet your needs.

The second hold isn't necessary. You can do your report right there. Or hold the results and then do the report.

FYI, learning how to create and use HOLD files is a basic concept you should master in WebFOCUS. It is very powerful.

Also, your tables are very small so the MATCH technique would work as well.

There is another technique you could use because your notes file is so small. It is called the WHERE IN FILE technique but since you are new to WebFOCUS, I will resist explaining it to you, at least today. Maybe tomorrow if you can't get past this point. BTW, let me know if the notes table will grow to be bigger than than and how many characters there are in the transaction field.

My earlier post suggesting use of SET ALL = SQL was in error because that feature will not be available until release 7.7. With current releases the problem with trying to find missing (NULL) values in the child/foreign table of a JOIN with SET ALL = ON is that the selection criteria (WHERE child/foreign column IS MISSING) causes the SQL translator to issue an INNER JOIN instead of an OUTER JOIN, regardless of the SET ALL setting and regardless of the JOIN syntax used (e.g., JOIN LEFT_OUTER). The work-around is to use SET ALL = PASS. The drawback to this work-around is that if there are multiple tables being joined, all of the JOINs will be translated to LEFT OUTER joins, again, regardless of the JOIN syntax used (e.g., JOIN INNER). In cases such as this one, where there is only one JOIN and the goal is to find missing values in the child table, SET ALL = PASS should provide a workable solution.

SET ALL = PASS
-*
JOIN TRANSACTION_NUMBER IN INVOICE_NOTES TO TRANSACTION_NUMBER IN DETAILED_AGING AS J1
-*
TABLE FILE INVOICE_NOTES
 PRINT TRANSACTION_NUMBER
 WHERE DETAILED_AGING.TRANSACTION_NUMBER IS MISSING ;
END

This message has been edited. Last edited by: Dan Satchell,

Dan, your suggestion worked but I had to change the where because it was returning both transaction numbers with and without an invoice attached to them.

This is what my where looks like and it works.

 Where( INV_NOTES.TRNSCT_NUM NE DETAIL_AGING.TRNSCTN_NUMBER )  

Thank you all for your help!