Yeah, I know, it's a stupid question. But since we've upgraded to 8.2 I've had one **** of a time getting left outer joins to work. Code Below --

-SET ALL=ON
JOIN CLEAR *
JOIN LEFT_OUTER UNITID IN CTM_QFUNIT TO ALL UNIT_ID IN INTERACTIONS AS J1;

-* Get data for all branches, including closed ones:
TABLE FILE CTM_QFUNIT
PRINT *
BY UNITID
WHERE EXTREF CONTAINS 'Offices'
-*WHERE RECORDLIMIT EQ 100
WHERE (SERVICE_DT GE '&START_FIXED' AND SERVICE_DT LE '&END_FIXED') OR SERVICE_DT EQ MISSING;
ON TABLE SET ALL ON
END
-EXIT

I am doing everything I can to have this code return units that don't have interactions in them, and they just don't show up. I've thrown in all the bells and whistles to try to convince the compiled select to bring back all the units.

This is my third report dealing with this issue, and I've been working with FOCUS since 1998. This is, like 102 class material.

Just a sanity check -- has something changed in 8.2 that makes left outers (quite frankly, "ALL" joins work differently?

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

-SET ALL=ON is not valid syntax, it is not a Dialogue Manager command... DM commands end with a semi-colon as well...

The syntax is:

SET ALL = {ON|OFF|PASS}
where:

ON
Includes missing segment instances in a report when fields in the segment are not screened by WHERE or IF criteria in the request. The missing field values are denoted by the NODATA character, set with the NODATA parameter (for more information, see NODATA).

OFF
Omits missing segment instances from a report. OFF is the default value.

PASS
Includes missing segment instances in a report, regardless of WHERE or IF criteria in the request. This option is not supported when MULTIPATH = COMPOUND (see MULTIPATH).

What type of files are you joining?
FOCUS?
SQL?
If FOCUS shouldn't you use SET ALL=PASS?
If SQL I think you should use SET SHORTPATH=SQL

Yeah, I tried the SET ALL the proper way first, what you're seeing are the desperate attempts to get anything to change.

This is two SQL*Server tables in the same database. When I select on one of them first and drop the contents into a hold file I can make Left Outer work. But jeeze louise, this is about as simple a select as you can get. I've done this dozens if not hundreds of times in the past in prior versions.

I'm more than a little concerned of what is going to happen when my prior clients upgrade.

SET SHORTPATH=SQL

JOIN CLEAR *
JOIN LEFT_OUTER UNITID IN CTM_QFUNIT TO MULTIPLE UNIT_ID IN INTERACTIONS TAG I2 AS J1;

-* Get data for all branches, including closed ones:
TABLE FILE CTM_QFUNIT
PRINT *
BY UNITID
WHERE EXTREF CONTAINS 'Offices'
ON TABLE SET ALL ON
WHERE (SERVICE_DT GE '&START_FIXED' AND SERVICE_DT LE '&END_FIXED') OR SERVICE_DT IS MISSING
END
-RUN
  

No Change.

Take out the WHERE filters, HOLD the data and perform your WHERE filters on the HOLD file...

quote:

Take out the WHERE filters, HOLD the data and perform your WHERE filters on the HOLD file...

Yeah, I'm actually making this work the other way around. I'm performing the select on the child (with its WHEREs) first, and holding the file format focus. Then I left-outer join to that focus file and it works. I'd pull 30 million records into the hold if I do it without WHERE clauses.

The left outer is only failing when I'm calling between two SQL*Server tables.

I've done this kind of thing for a couple of decades and have never run into this before, so something has changed. We just upgraded to version 8.2.

Thanks for the responses. This is a bit of a sanity check question. It likely means that I'll put Holds before all my left-outers going forward regardless, just to protect against version differences.

What MS SQL adapter are you using? MSODBC or the older SQLMSS?

This may/may not point you at an issue. If your previous version used SQLMSS and you're now using MSODBC then flip the adapter being used and try again.

I would hazard a guess that your field SERVICE_DT is in the INTERACTIONS table? If so, by adding a where on a field on the second table the RDBMS is inferring an inner join and cancelling any attempt that you do to enforce a left outer join.

If this is a join that you use often, then consider using a cluster join or even a DB view.

Above all, make a note using comments in your code or synonym to record the changes that you've made so that someone coming along at a later date knows!!

T

Hi John,

We have no issue with Left outer joins with MS SQL Server, although if we mix inner and left joins in the same request we usually need SHORTPATH=SQL.

Have you tried turning on SQL traces to see what query the Reporting Server is building? It might give you a hint on where the issue lays.

Also is SERVICE_DT in the parent or child?
If in the child try SERVICE_DT EQ MISSING rather than IS MISSING.

Cheers

Stu

Yeah I used EQ MISSING to start and there was no change in behavior.

Service_Dt is indeed in the child table, and I've done this plenty of times in the past, like, maybe hundreds over the past 20 years. One of my coworkers struggled with the same thing when we moved to 8.2 just recently, so something has changed.

When we pull one of the tables out of SQL*Server into a Hold file and join against that instead all functions as it used to. We've declared that best-practice and we're moving on. We're in panic mode right now, providing critical insight to a sudden change in business practices due to lockdowns of the offices. So for the moment this is going to suffice.

But I'll add that all of this is via the SQL*Server adapter.

Someday I may open a case. Here's hoping things settle down in a few weeks.

Since you're using a field from the right-hand side of the JOIN in your WHERE-clause, the JOIN gets converted to an INNER join - just as it would in SQL.

A solution I frequently use in that case is to use a conditional join instead.

JOIN
    FILE CTM_QFUNIT AT UNITID TAG u TO MULTIPLE
    FILE INTERACTIONS AT UNIT_ID TAG i AS J1

    WHERE i.UNIT_ID EQ u.UNITID;
    WHERE (SERVICE_DT GE '&START_FIXED' AND SERVICE_DT LE '&END_FIXED') OR SERVICE_DT IS MISSING;
END
-* Get data for all branches, including closed ones:
TABLE FILE CTM_QFUNIT
PRINT *
BY UNITID
WHERE EXTREF CONTAINS 'Offices'
END
-RUN

If SERVICE_DT is never MISSING in INTERACTIONS though, then you are actually attempting to do an INNER JOIN...

If I run into issues like this, I usually put the statement within a trace (search the forum for the commands to enable that), to see what decisions WebFOCUS made to perform the query.

If it says it's doing a FOCUS managed join, for example, it pays to look at the reasons for that choice and fix those if possible - because that is hardly ever what you want to happen...

I'll tell you what -- we pre-select on two tables into FOCUS files and then perform the join in WF or DM and get dramatically increased performance. I'm talking sometimes 98% improvement in speed.

There's still plenty of reason to custom-code your joins.

This problem is fixed folks! I have a viable strategy that does just what I need. Thanks!

Unless you were joining tables from different databases or are querying some terribly inefficient RDBMS server... Something is quite wrong there.

IMHO, you should analyse why the performance of the join at the RDBMS side is so much worse than doing it in WF, because that's opposite to expectations. You're asking an RDBMS for something it's supposed to be good at.

What may be happening is that you got a FOCUS-managed join in the generated code, for which you can look at the trace output of your TABLE request.
Those tend to result in going over all records at one side of the join, running a query to match the counter-part for every row in that set - in the majority of cases that's a horrible idea.
For causes, those can be various. For example, I've seen it happen that the access-files describing the RDBMS tables had different server identifiers, making WF think they were on different servers.

Another probable cause is a lack of an index on one of the columns you join on.

Whatever's the case, doing what you do, fetching the two result sets separately and then joining them in FOCUS, is not going to scale well once your datasets grow.

Option 2 is to write six extra lines of code, get superb results and move on. My clients pay me by the hour so it's just not in their best interest to debug the generated select.

This has been most effective on my tens-of-millions-of-records joins. Focus and XFocus files have been kicking a$s and don't require a re-review of the internally-generated select structure whenever a code change or upgrade is requested. My code is tight, immediately apparent, easy to understand and works.

It also is a standard best-practice solution that works across my cross-platform joins, DB2, SQL*Server, Oracle and Access. That's my current landscape.

Not meaning to reject your recs out-of-hand, but I need to keep my client's best interests at heart and that doesn't include deep analysis of how WebFOCUS' internals work.

Well done, John.
I also find that FOCUS files are very fast.
Since you are building your FOCUS files from your extracted data, consider also the possibility not using JOIN but creating one hierarchical FOCUS file. You will have physical pointers between parent and child.
Keep the clients happy and healthy!