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]Left Outer Join

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Left Outer Join
 Login/Join
 
Platinum Member
posted
I am trying to do a left outer join (J0 below) but when I put a filter on the table that I have left outer joined to, sgrchrt, it only returns the rows who have a value that is equal to TAC$

JOIN
LEFT_OUTER SHRTCKN.SHRTCKN.SHRTCKN_PIDM IN shrtckn TO UNIQUE
SGRCHRT.SGRCHRT.SGRCHRT_PIDM IN sgrchrt TAG J1 AS J1
END
TABLE FILE SHRTCKN
BY LOWEST SHRTCKN.SHRTCKN.SHRTCKN_PIDM
WHERE ( SHRTCKN.SHRTCKN.SHRTCKN_TERM_CODE EQ '&TERM_CODE' )
AND ( J1.SGRCHRT.SGRCHRT_CHRT_CODE EQ 'TAC$' )
AND ( SHRTCKN.SHRTCKN.SHRTCKN_PIDM EQ 247907 OR 242099 OR 250619 OR 253652 );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET HTMLCSS ON
END

Thanks

This message has been edited. Last edited by: <Emily McAllister>,


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Virtuoso
posted Hide Post
What kind of database are you reporting off of? If this is a SQL database, turn on SQL Trace and look at the resulting SQL.

You can add the following to capture the trace
SET XRETRIEVAL = OFF
SET EMPTYREPORT = OFF
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF
SET TRACEUSER = ON


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
When I do a trace I get

FOC2525 - FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: SGRCHRT
FOC2509 - RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE
FOC2524 - JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY

I am joining two oracle database tables


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Expert
posted Hide Post
Hi Trudy,
When you use the LEFT_OUTER JOIN syntax, AND, use a compound WHERE in the initial extract that affects both DB's, WebFOCUS will convert to an INNER JOIN.
Don't know why, but, that is what happens, as I have come to find out.
So, to circumvent, just dump on the SHRTCKN_TERM, HOLD the data, including the columns to be tested later, then, do the other WHERE against the HOLD file.

We use Banner and do it this way so as not to lose students/data...

hth, Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
i also tried the SET ALL=ON


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Virtuoso
posted Hide Post
You don't get any SELECT in the trace?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
Tom, thanks for this. I was hoping there was another way rather than constantly dumping things out to hold files.


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Virtuoso
posted Hide Post
If you go to the documentation and search for optimization join oracle, there is very specific information on Optimizing Non-equality WHERE-based Left Out Joins in the Adapter Administration> Using the Adapter for Oracle. That might explain what's happening.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
Here is the complete trace, I just pasted the error messages, sorry.

-DEFAULTH TERM_CODE=_FOC_NULL;
-SET &TERM_CODE=IF _FOC_NULL EQ '_FOC_NULL' THEN 201610 ELSE _FOC_NULL;
-SET ALL=ON
JOIN
LEFT_OUTER SHRTCKN.SHRTCKN.SHRTCKN_PIDM IN shrtckn TO UNIQUE
SGRCHRT.SGRCHRT.SGRCHRT_PIDM IN sgrchrt TAG J1 AS J1
END
TABLE FILE SHRTCKN
BY LOWEST SHRTCKN.SHRTCKN.SHRTCKN_PIDM
WHERE ( SHRTCKN.SHRTCKN.SHRTCKN_TERM_CODE EQ '201610' ) AND ( J1.SGRCHRT.SGRCHRT_CHRT_CODE EQ 'TAC$' ) AND ( SHRTCKN.SHRTCKN.SHRTCKN_PIDM EQ 247907 OR 242099 OR 250619 OR 253652 );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET HTMLCSS ON
END
FOC2525 - FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: SGRCHRT
FOC2509 - RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE
FOC2524 - JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
AGGREGATION DONE ...
SELECT
T1."SHRTCKN_PIDM",
SUM(T1."SHRTCKN_PIDM")
FROM
SATURN.SHRTCKN T1,
SATURN.SGRCHRT T2
WHERE
(T2."SGRCHRT_PIDM" = T1."SHRTCKN_PIDM") AND
(T1."SHRTCKN_PIDM" IN(247907, 242099, 250619, 253652)) AND
(T1."SHRTCKN_TERM_CODE" = '201610') AND
(T2."SGRCHRT_CHRT_CODE" LIKE 'TAC_')
GROUP BY
T1."SHRTCKN_PIDM"
ORDER BY
T1."SHRTCKN_PIDM";
0 NUMBER OF RECORDS IN TABLE= 2 LINES= 2
0 HOLDING HTML FILE ON PC DISK ...


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Virtuoso
posted Hide Post
Are you actually searching for a SGRCHRT_CHRT_CODE value of TAC$ or is the $ sign there for a wildcard search?

EQ 'TAC$' is turning into a LIKE 'TAC_' in your SQL which is causing the problem. The SQL is showing:
(T2."SGRCHRT_CHRT_CODE" LIKE 'TAC_')


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Expert
posted Hide Post
Trudy,
SHRTCKN is a historical table with all terms, courses, grades, etc.
SGRCHRT is a Cohort Academic table with 1 record per PIDM.
With your code, you'll get every STUDENT record for all students in 201610, Fall 2015, whether on not they are in SGRCHRT; is that what you want?

We never use SHRTCKN as a HOST file, try reversing your JOIN, and, adding TERM CODE.

JOIN SGRCHRT_PIDM AND SGRCHRT_TERM_CODE_EFF TO ALL SHRTCKN_PIDM AND SHRTCKN_TERM_CODE IN SHRTCKN AS J1
-RUN
TABLE FILE SGRCHRT
ETC.


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
Just to see if the SQL changes you may want to try J1.SGRCHRT.SGRCHRT_CHRT_CODE EQ 'TAC'|'$'
instead of SGRCHRT_CHRT_CODE EQ 'TAC$'.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Expert
posted Hide Post
Why would that make a difference???
It should be EDIT(J1.SGRCHRT.SGRCHRT_CHRT_CODE,'999') EQ 'TAC' anyways...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
When you have a WHERE FIELD EQ 'xyz$' you're basically telling it you want all values that start with xyz. However, with WHERE FIELD EQ 'xyz'|'$' you're actually looking for the single value of xyz$. In this case, I'm not sure what the intention is. Is it a wildcard or an exact search. If it's the wildcard, it will translate to a WHERE FIELD LIKE.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Expert
posted Hide Post
I've been using FOCUS/WebFOCUS for over 30 years, please don't try to explain things to me, appreciate it...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
Try this setting before your query:

SET SHORTPATH = SQL


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
Did you also try SET ALL=PASS


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
Trudy,

Your issue is that you have your "SGRCHRT_CHRT_CODE = 'TAC$' "restriction in the where not the join. WebFOCUS will not recognize that you meant to construct the sql like such:

Select * 
FROM SHRTCKN
     LEFT OUTER SGRCHRT
       ON SHRTCKN_PIDM = SGRCHRT_PIDM
         AND SGRCHRT_CHRT_CODE LIKE 'LAC%' WHERE SHRTCHN_TERM_CODE EQ '&TERM_CODE'
  AND SHRTCKN_PIDM IN (....)
GROUP BY SHRTCKN_PIDM DESC;


For something like that you need to specify that in the join, just like you would for SQL (but syntax is different). Maybe you are trying for something like this?

-DEFAULTH TERM_CODE=_FOC_NULL;
-SET &TERM_CODE=IF _FOC_NULL EQ '_FOC_NULL' THEN 201610 ELSE _FOC_NULL;
-SET ALL=ON

 JOIN
 LEFT_OUTER FILE SHRTCKN AT SHRTCKN.SHRTCKN.SHRTCKN_PIDM TO UNIQUE FILE SGRCHRT
 AT SGRCHRT.SGRCHRT.SGRCHRT_PIDM TAG J0 AS J0
 WHERE (SHRTCKN.SHRTCKN.SHRTCKN_PIDM EQ J0.SGRCHRT.SGRCHRT_PIDM) AND (SGRCHRT.SGRCHRT.SGRCHRT_CHRT_CODE EQ 'TAC$');
 END
TABLE FILE SHRTCKN
BY LOWEST SHRTCKN.SHRTCKN.SHRTCKN_PIDM
-* This is only here to invoke the join to SGRCHRT, without it there are no fields from the second table and as such, the join would be ignored
BY LOWEST SGRCHRT.SGRCHRT.SGRCHRT_PIDM NOPRINT
WHERE ( SHRTCKN.SHRTCKN.SHRTCKN_TERM_CODE EQ '&TERM_CODE' ) 
AND ( SHRTCKN.SHRTCKN.SHRTCKN_PIDM EQ 247907 OR 242099 OR 250619 OR 253652 );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET HTMLCSS ON
END


Although, in this case the join has no impact either way, so i'm suspecting maybe you are trying to accomplish something different?

This message has been edited. Last edited by: eric.woerle,


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Platinum Member
posted Hide Post
In SQL, a Left Outer Join with a conditional on the right hand side is basically equivalent to an inner join. I.e., this:
  
JOIN LEFT_OUTER A To B etc etc
WHERE B.some_field = some_value
 

will only return rows that satisfy the condition on B.some_field. If there is no matching row in B for a particular row in A, the row in A will be filtered out. I.e., SQL is doing an inner join.

Something like this may work for you:
  
JOIN LEFT_OUTER A To B etc etc
WHERE (B.some_field = some_value) OR (B.some_field IS NULL)
 
Posts: 164 | Registered: March 26, 2003Report This Post
Virtuoso
posted Hide Post
If you write the relevant part of the query as a CONDITIONAL JOIN (aka WHERE-based JOIN) and include the troubling WHERE-clause there, WebFOCUS will generate a correct outer join.

JOIN LEFT_OUTER
 FILE shrtckn AT SHRTCKN.SHRTCKN.SHRTCKN_PIDM TAG J1L TO UNIQUE
 FILE sgrchrt AT SGRCHRT.SGRCHRT.SGRCHRT_PIDM TAG J1R AS J1

 WHERE J1R.sgrchrt EQ J1L.shrtckn;
 WHERE J1R.SGRCHRT_CHRT_CODE EQ 'TAC$';
END
TABLE FILE SHRTCKN
BY LOWEST SHRTCKN.SHRTCKN.SHRTCKN_PIDM
WHERE ( SHRTCKN.SHRTCKN.SHRTCKN_TERM_CODE EQ '&TERM_CODE' )
AND ( SHRTCKN.SHRTCKN.SHRTCKN_PIDM EQ 247907 OR 242099 OR 250619 OR 253652 );

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET HTMLCSS ON
END


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
Master
posted Hide Post
FYI WEP,

Your query will ignore SGRCHRT all together. There are no fields from SGRCHRT in the TABLE FILE request, so the join will be ignored. See my comment in my code above where I mention it.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Virtuoso
posted Hide Post
Ah yes, good point Eric!

While I certainly don't like the behaviour of WF where it likes to optimize away things that we explicitly mention in a query (unless we fool it into thinking that it can't), in this case there's nothing wrong with that!

Seeing as this is a left join and none of the right-side fields are used, why do we bother with the join?
My guess is that the original example is incomplete?


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
Master
posted Hide Post
HAHA!

I'm the opposite. I love the behavior. I take extensive advantage of it in my Master Clustered Joins (or whatever the technical name is). I'm currently scratching my head, because I recently installed 8.1.05 onto a sandbox, and its invoking the ENTIRE join and I can't figure out why... Tech Support loves to tell us to just upgrade to the next version, your problem will be fixed there... Sure....


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Master
posted Hide Post
Although, I guess if you wanted to make sure the join was invoked everytime, then you could use EricH's suggestion. I used to do it that way before they added Conditional Join to the product.

See we both get to be happy! Cool


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report 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]Left Outer Join

Copyright © 1996-2020 Information Builders