I think I might just need to add in a setting but I have say 2 tables AAA as A and BBB as B.

In SQL I would
Select *
From AAA as A
Left Join BBB as B on A.1 = B.1
Where B.1 is null

In WF the join seems to work but in the report I get nothing when I Where/IF B.1 "Is Missing".

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

Try this
ON TABLE SET HOLDMISS {ON|OFF}

If this doesn't work...
Can you describe with more info. This will helps to suggest in right direction.

Try saying

SET ALL=ON

before the TABLE request. That will give you the parent row if the child row doesn't exist.

If you are going against relational data, though, I'd be careful and turn a trace on before to make sure that the left join gets passed. Not all adapters are at the same level and in many cases SET ALL turns off optimization.

Here are the trace commands:

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON
SET XRETRIEVAL=OFF

I really don't understant what exactly your trying to accomplish with that SQL up there. You're trying to do an outer join on a table where only the key is null? That really shouldn't work too well unless your join fields contain nulls somehow.

Maybe what you were trying to do was an exception join? Something like left outer join on field1 where any_field_in_right_side_table_other_than_join_field is null.

As I understand, such LEFT OUTER JOIN is equivalent to NOT EXISTS :

SELECT  *
From AAA as A
WHERE 
 NOT EXISTS(
   SELECT * FROM BBB
    WHERE B.1 = A.1
 )

Check this topic

Hope this helps

This is a bug in WebFOCUS and should be fixed since it is a VERY COMMON task to want to find the OLD-NOT-NEW intersection (MATCH terminology). I would suggest you raised this issue with IBI as a case.

The issue is that webFOCUS is a conglomeration of FOCUS's original behaviour of ALL. prefix and SET ALL=OFF|ON|PASS and the relational database method of specifying misssing data through the nature of the JOIN.

With LEFT_OUTER it is natural to assume that this is a direct replacement for the rdbms LEFT OUTER JOIN but it is still subject to SET ALL behaviour.

This behaviour should be changed or clarified in the manual since it is very confusing!

Example

SET ALL=OFF
JOIN LEFT_OUTER WOR_PROJECT_ID IN WORK_ORDER TO PRJ_PROJECT_ID IN PROJECT AS J1 
TABLE FILE work_order 
PRINT WOR_PROJECT_ID WHERE PRJ_PROJECT_ID IS MISSING  
END 

The adapter optimizer figures that, since there is a WHERE test on the target table, it can downgrade the JOIN from a LEFT OUTER to an INNER JOIN since ALL=OFF. (This is infact true for all equality tests except for MISSING)

SELECT T1."PROJECT_NO",T2."PROJECT_ID" FROM IFSAPP.WORK_ORDER 
T1,IFSAPP.PROJECT T2 WHERE (T2."PROJECT_ID" = T1."PROJECT_NO") 
AND T2."PROJECT_ID" IS NULL ; 

LEFT_OUTER should be independent of ALL setting but this does what you want.

SET ALL=PASS 
JOIN LEFT_OUTER WOR_PROJECT_ID IN WORK_ORDER TO PRJ_PROJECT_ID IN PROJECT AS J1 
TABLE FILE work_order 
PRINT WOR_PROJECT_ID WHERE PRJ_PROJECT_ID IS MISSING  
END 

It correctly translates to

SELECT T1."PROJECT_NO",T2."PROJECT_ID" FROM ( 
IFSAPP.WORK_ORDER T1 LEFT OUTER JOIN IFSAPP.PROJECT T2 ON 
T2."PROJECT_ID" = T1."PROJECT_NO" ) WHERE T2."PROJECT_ID" IS 
NULL ; 

Thanks for all the help, so far I still can't seem to get it working.

I have 2 tables, Work table and reviewed table. I want all the accounts in work that do not have a "review_date" in the review table. So kind of like this:

Select a.*
from Work as a
Left join review as b on a.account_no = b.account_no
where b.review_Date is null

I tryed the set: ALL = pass but it didn't work. In WB the join is work and review (review is a single, left Outer join), and then in my report under the where/if I'm using the review_date "is missing" and many other ways but I still can't get it right.... I hope that description is better to understand.

Have you told us what your data source type is yet? Is it SQL Server, DB2, etc.? Some data source adapters don't pass the left outer join (Teradata for instance).

Are you using SQL Passthru or master file descriptions?

Is it possible to have a row in the review table that has a matching account_no and no review_Date? If yes, maybe the date is not null; maybe it is zero or something.

What happens when you select just from the review table? What do you see in review_Date?

Are the tables very big? If not, you can retrieve the data from both tables, put the review data in a FOCUS file, then do a join with SET ALL=ON and you would get what you wanted.

Johnny

Please post all your code and we can get to the bottom of this.

Read this and make sure KEYS=0 does NOT appear in your access file.

http://documentation.informationbuilders.com/masterinde...13snf/rep_lang11.htm


John

In the above HREF to the document it does mention

LEFT OUTER

Specifies a left outer join. If you do not specify the type of join in the JOIN command, the ALL parameter setting determines the type of join to perform.


INNER

Specifies an inner join. If you do not specify the type of join in the JOIN command, the ALL parameter setting determines the type of join to perform.

The behaviour described in the examples above directly contradicts these statements.

I can raise this as a Hottrack but I would like some idea of the likelihood of success from any IB'ers out there.

1. Is my argument sound?
2. Is this old chestnut going to be trotted out? : It's been in the product for so long we can't fix it for fear of breaking code that's already written.

Select a.*
from Work a EXCEPTION join review b on a.account_no = b.account_no

Here's a possibility. Don't know if it will work with non-FOCUS files, though.

SET ALL = PASS

(join A to B)

TABLE FILE...
.
.
WHERE B.1 NE B.1
END

B.1 being pretty much any field from B

I know. It looks goofy.

Now raised as case 40462527

When I had to do this, I did a left join and created a hold file. I then did a where 'any field from child' is missing for my selection. This will give you all parent records that have no child records.

What's happening here (I think) is that the SQL translator tries to generate the most efficient SQL possible. If you have a left outer join and you place a WHERE clause on the right hand side, then (under most circumstances) it's just as if you were doing an inner join. So in this example, if you were looking for a specific review_date there would be no point in doing an outer join, since any accounts in your work table that did not have a review date would not appear in your result set anyway.

That said, checking for a null value on the right hand side is the one exception to this behavior. While this DOES work in SQL, it is not the recommended method, since it depends on the field on the right hand side being non-nullable The recommended approach (as someone pointed out) is to use the SQL NOT EXISTS operator.

In the meanwhile, you've got a business problem to solve. I tried hammo1j's solution with SET ALL=PASS. It generated the correct SQL, however I got no rows in my output. This was puzzling. After some experimentation, I hit the jackpot. The solution was to do a JOIN TO ALL in addition to SET ALL=PASS. That generated the correct SQL and gave me the correct output.

I did my testing using SQL*Server database.

If this does not work for you, then another solution would be to do a two pass query. You would do your outer join in the first pass but without the where clause. This will return all rows in your work table. Hold those results - make sure to SET HOLDMISS ON! Then do a TF against the HOLD file and now filter on your missing value from the review table.

Good luck!

EricH

It's very sad that FOCUS does not have ANTIJOIN (NOT EXISTS) and SEMIJOIN (EXISTS)

LEFT OUTER JOIN variant is not equivalent.
(Even when it works)

Suppose we have two tables: TABLE_A, TABLE_B.
In case when TABLE_B have less or equal quantity of rows with TABLE_B - LEFT OUTER JOIN is equivalent with ANTIJOIN.

In case when TABLE_A is little and TABLE_B is big everything is worse.
A - is dimension table, and B is fact_table.

ANTIJOIN:
SELECT * FROM TABLE_A A
WHERE
NOT EXISTS(
SELECT * FROM TABLE_B B
WHERE
B.ID = A.ID
)

If we make TABLE_A LEFT OUTER JOIN TABLE_B => we have multiplication of rows in TABLE_A.
Of cause we can suppress it with DISTINCT.
But it's very inefficient -> we force RDBMS make multiplication and then force to remove duplicates.

SEMIJOIN:
SELECT * FROM TABLE_A A
WHERE
EXISTS(
SELECT * FROM TABLE_B B
WHERE
B.ID = A.ID
)

Again, we can make it with JOIN and DISTINCT, but situation is even worse :
When RDBMS makes EXISTS-check it needs to find only one row in TABLE_B, not all rows. It's not needed to scan all rows in TABLE_B. EXISTS can be much faster then JOIN.

Can somebody tell me how to write New Feature Request to IBI?

Regards,
Alex

Ingas:

The left outer with screen on missing/null is ALWAYS equivalent to your anti-join example and you should always have a primary key in the target table to test for null since rdbms' (unlike wf) do not allow a null in a pk. (wf I believe is more sensible as usual!)

The cost should not be different as a good optimizer should come up with the best method in either method.

I agree there is no wf equivalent of the semi join, because of the multiplicative effect.

It would be cool if they upgraded the INCLUDES and EXCLUDES IF test facility (which does not work with rdbms only with wf database) so that a SEMI or ANTI is automatically generated.

Here's a good book on the subject of OUTER JOINS

http://books.google.com/books?id=6aMJH43GwpIC&pg=PA17&l...Aazm7cfZtJsA#PPP1,M1

Important points to note

1. Order of LEFT_OUTER joins cannot be changed to produce same results.
2. The WHERE clause implicit in the JOIN CANNOT be replicated by putting the WHERE clause in the query in the normal way. There are 2 phases of exclusion.

According to the author OUTER joins result in a 'hierarchic structure' and he postulates about a dialect of SQL which recognises the hierarchy in terms of counts and duplicated records etc.

This is WebFOCUS! So Gerry was right all along...

Hallo John:
Yes, you're correct with ANTIJOIN :
Results with LEFT OUTER and TABLE_B.PK_Field IS NULL - always equivalent with no multiplicative effect.

In terms of efficiency problem remains : RDBMS will make unnecessary join and then filter null values.

Now I prefer to write SQL when I need complex JOINs.

Just got this back from IBI

Your IBI Case#40462527 internal: 000160430

Product management has informed me that this issue will be looked into for the
7.7 release of the reporting server.

So you will have to wait until 7.7 until this is possibly fixed.

Regards

John

John,

Thanks very much for the detailed explanation.

I don't believe this will be corrected in v7.7 even though "this issue will be looked into for the 7.7 release of the reporting server".

I will open a case as well.

Regards,

quote:

Originally posted by RSquared:
When I had to do this, I did a left join and created a hold file. I then did a where 'any field from child' is missing for my selection. This will give you all parent records that have no child records.

I agree with Rsquare. As soon as you touch the fields in B-file, WF turns the outter-join to inner-join.

Hua