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
ThanksThis message has been edited. Last edited by: <Emily McAllister>,
WF8 Windows
January 22, 2016, 02:54 PM
BabakNYC
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
January 22, 2016, 03:04 PM
Trudy
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
January 22, 2016, 03:06 PM
Tom Flynn
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...
Tom, thanks for this. I was hoping there was another way rather than constantly dumping things out to hold files.
WF8 Windows
January 22, 2016, 03:35 PM
BabakNYC
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
January 22, 2016, 03:42 PM
Trudy
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
January 22, 2016, 03:49 PM
BabakNYC
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
January 22, 2016, 03:53 PM
Tom Flynn
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.
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
January 22, 2016, 04:09 PM
Tom Flynn
I've been using FOCUS/WebFOCUS for over 30 years, please don't try to explain things to me, appreciate it...
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
February 01, 2016, 10:50 AM
EricH
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)
February 01, 2016, 11:24 AM
Wep5622
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 :
February 01, 2016, 11:38 AM
eric.woerle
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
February 02, 2016, 04:06 AM
Wep5622
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 :
February 02, 2016, 02:44 PM
eric.woerle
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
February 02, 2016, 02:47 PM
eric.woerle
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!
Eric Woerle 8.1.05M Gen 913- Reporting Server Unix 8.1.05 Client Unix Oracle 11.2.0.2