QBE with left outer join

Hi,

I'm using a Business View with a left outer join of 2 tables: TableA.id = TableB.id(+)
The Problem is, that the QBE-Line doesn't work right with the TableB.
If I use SQLMonitor to get the SQL Statement OneWorld executes, I can see that the SQL condition contains "WHERE ... AND (TableB.id = 123 OR TableB.id IS NULL)".
I get all rows with the ID = '123' or NULL, but I only want the rows with '123'.
Is that possible?

Ednax

By definition, a left outer join gives you all records in the left table and all matching records in the right table. When there is no matching record in the right table, it is filled with NULL fields.

In your table select, set TableA.id = 123 and you will get only the records where TableA.id = 123.

When querying against fields that may have a null value the standard is treat null values as meeting the condition - or better stated that the condition does not apply when a null is present.

So yes, you will see the equivalent of "(TableB.id = 123 OR TableB.id IS NULL)". The only reason I can think of as to why JDE was redundant (the or IS NULL clause) in the generated SQL is to insure common behavior across platforms.

Cheers,

thank you

So, it is not possible to change this behaviour?

I intend to suppress the lines containing NULL in TableB.id when filtering one of the fields of TableB.
That should work sufficiently.

If you suppress TableB.id IS NULL records, you will also be suppressing TableA.id records that have no matching record in TableB. If this is your intent, why not just do a simple join?

A simple join won't work because I want to see the TableA records even if the TableB record is inexistent. But ONLY if the user does not set a filter at one of the fields from TableB.
If the user sets a filter at a field from TableB, then only the records matching this filter should be shown. That excludes records with TableB.id=NULL.