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>,

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

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

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

i also tried the SET ALL=ON

You don't get any SELECT in the trace?

Tom, thanks for this. I was hoping there was another way rather than constantly dumping things out to hold files.

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.

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 ...

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_')

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.

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$'.

Why would that make a difference???
It should be EDIT(J1.SGRCHRT.SGRCHRT_CHRT_CODE,'999') EQ 'TAC' anyways...

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.

I've been using FOCUS/WebFOCUS for over 30 years, please don't try to explain things to me, appreciate it...

Try this setting before your query:

SET SHORTPATH = SQL

Did you also try SET ALL=PASS

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,

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)

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

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.

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?

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....

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!