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     [SOLVED] Is it possible to join and filter by the same field?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Is it possible to join and filter by the same field?
 Login/Join
 
Member
posted
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,


WebFOCUS 7.6.9
Windows
all output (Excel, PDF, HTML)
 
Posts: 9 | Registered: March 19, 2010Report This Post
Expert
posted Hide Post
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?


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
Virtuoso
posted Hide Post
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


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Member
posted Hide Post
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,


WebFOCUS 7.6.9
Windows
all output (Excel, PDF, HTML)
 
Posts: 9 | Registered: March 19, 2010Report This Post
Virtuoso
posted Hide Post
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,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
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.


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
Member
posted Hide Post
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.


WebFOCUS 7.6.9
Windows
all output (Excel, PDF, HTML)
 
Posts: 9 | Registered: March 19, 2010Report This Post
Guru
posted Hide Post
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. Frowner

(I have repeated what Jinny said Smiler)
Hua

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


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Expert
posted Hide Post
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


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
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


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Expert
posted Hide Post
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.


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
Virtuoso
posted Hide Post
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,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Member
posted Hide Post
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!


WebFOCUS 7.6.9
Windows
all output (Excel, PDF, HTML)
 
Posts: 9 | Registered: March 19, 2010Report 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     [SOLVED] Is it possible to join and filter by the same field?

Copyright © 1996-2020 Information Builders