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     [CASE OPENED] Inverse left outer join: possible?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE OPENED] Inverse left outer join: possible?
 Login/Join
 
Wep5622
Virtuoso
posted
I'm trying to do a "reverse left outer join" between a couple of tables, but it appears that WebFOCUS incorrectly turns it into an inner join.

I have a table SAMPLE, containing _all_ our samples identified by a sample-id
and a table ISTEST that specifies whether a sample is real (0) or just a system test (1). Not all samples have a record in ISTEST.
Both tables are in the same Oracle 11g database.

Now, naturally the amount of system tests compared to real samples is very small in comparison. In our data, less than 4% of the records have ISTEST=1.
A query to weed out the tests will be a lot faster than one that attempts to confirm that every sample in the result is a real sample.

Hence, I tried to do this:
JOIN LEFT_OUTER
 FILE SAMPLE AT SAMPLEID TAG sample TO UNIQUE
 FILE ISTEST AT SAMPLEID TAG istest AS J0

 WHERE sample.SAMPLEID EQ istest.SAMPLEID;
 WHERE istest.ISTEST EQ 1;
END
TABLE FILE SAMPLE
PRINT *
WHERE istest IS MISSING;
END


Unfortunately, WebFOCUS turns that into this inner join, which is not equivalent to the above!:
 SELECT
 T1."SAMPLEID",
 T2."ISTEST",
 T2."SAMPLEID"
 FROM
 SAMPLE.SAMPLE T1,
 SAMPLE.ISTEST T2
 WHERE
 (T2."SAMPLEID" = T1."SAMPLEID") AND
 (T2."ISTEST" = '1') AND
 (T2."ISTEST" IS NULL);


Now what? Is this a bug?

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


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
MartinY
Virtuoso
posted Hide Post
Your JOIN is an INNER, without specification it use INNER by default if I'm not mistaking.

Try to reverse the table order in your JOIN. WF only provides INNER JOIN or LEFT_OUTER JOIN.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Wep5622
Virtuoso
posted Hide Post
That was a typo, the actual query was run with a LEFT_OUTER - corrected, thanks for pointing that out.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
John_Edwards
Virtuoso
posted Hide Post
Look up MATCH FILE. It's pretty outrageous. I believe the NEW-NOT-OLD clause is the one you're looking for.

Joins can get might ugly at times, MATCH keeps things a little more even-keeled.

J.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Wep5622
Virtuoso
posted Hide Post
Interesting idea to use MATCH FILE for this situation, I hadn't thought of that.

Unfortunately it appears that MATCH FILE performs two separate queries for this case, which still isn't quite optimal: It means we're still downloading all test samples to the reporting server before reducing them to the relevant ones, something I'd rather have the database server perform.

Unless I did something wrong?:
MATCH
 FILE SAMPLE
 PRINT *
 BY SAMPLEID
 RUN

 FILE ISTEST
 PRINT ISTEST
 BY SAMPLEID
 WHERE ISTEST EQ 1;

 AFTER MATCH HOLD OLD-NOT-NEW
END


The trace output shows two separate SQL queries when I run the above.

Looks like I'll have to open a case with IBI to see whether we can get this implemented in FOCUS-style JOINs correctly.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
John_Edwards
Virtuoso
posted Hide Post
"Two separate queries" doesn't necessarily mean suboptimal, and doesn't necessarily mean most correct and most efficient to maintain time-wise and dollar-wise. I break joins apart all the time in order to keep things as simple as possible, and more often than not get fast results and simpler code.

Have you considered doing a Hold file for this? I'd be looking to invert the connection, either with a join or more likely a simple file connection in the where clause --

TABLE FILE ISTEST
BY SAMPLEID
WHERE ISTEST EQ 1
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS XYZ
END
-RUN

TABLE FILE SAMPLE
PRINT *
BY SAMPLEID
WHERE SAMPLEID NOT IN XYZ
END


I'm just riffin' that code so you may need to format the first result a bit but the concept should be workable and likely very fast.

J.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Tony A
Expert
posted Hide Post
quote:
WHERE istest.ISTEST EQ 1;

As soon as you perform an equality test on the subordinate table an inner join will jump up and bite you.

Try removing that test.

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
George Patton
Master
posted Hide Post
I'm with John on this. I use MATCH FILE a lot, but my data sets are fairly small < 100,000 records.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Wep5622
Virtuoso
posted Hide Post
quote:
Originally posted by Tony A:
quote:
WHERE istest.ISTEST EQ 1;

As soon as you perform an equality test on the subordinate table an inner join will jump up and bite you.

Try removing that test.

T


No it doesn't! That's one of the join conditions of the conditional outer join.

What causes the inner join is the clause:
WHERE istest IS MISSING;
and that's a requirement for this statement to work as intended.

The combination of both WHERE statements is what makes this an "inverse" outer join as opposed to a normal outer join.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Wep5622
Virtuoso
posted Hide Post
quote:
Originally posted by George Patton:
I use MATCH FILE a lot, but my data sets are fairly small < 100,000 records.


There's about 6 years of data in this database by now, resulting in about 13 million records in the top-level table of this report. Most of the samples are automatically created manufacturing samples, some (~5%) are manually added by our R&D division and only a few (<1000) are test samples.

This report is on a dashboard that displays live counts of samples in various stages of progress and for various audiences, it should have sub-second performance. It's nearly there, but it needs a few optimizations to get there and this looked like a good candidate for optimizing - currently the join is the long way around.

The issue isn't so much how we can optimise this query, the issue is that WebFOCUS does not seem capable of understanding an inverse outer join. Apparently it really doesn't, which is a shame.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
<Emily Duensing>
posted
I have had MATCH beat JOIN in many situations for optimizatiaon, so don't count it out. Make sure each "subquery" is as optimal as it can be, and let WebFOCUS do the rest; even with really large data sources. You might be surprised!
 
Report 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     [CASE OPENED] Inverse left outer join: possible?

Copyright © 1996-2020 Information Builders