Please excuse the poor readability, i couldn't figure out how to make underscores show up consistently. I'm using ASA 11 build 2331, 64 bit WinServ 2008.
This first select works ( it returns 6 rows as expected ). Note that
SELECT oh.c1, oh.c2, oh.c3, oh.c4, c.c1, e.moved-to-payment-history FROM customer c join order-header oh on c.customer-id = oh.customer-id left outer join eft-payment-history e on oh.invoice-number = e.invoice-number and e.moved-to-payment-history = 'n' WHERE oh.open-amount <> 0 and c.customer-id = '12345'
This select does NOT work ( no rows returned ). This is the SQL that I expected to work ( converted from tSQL *=, see below ). Note that
SELECT oh.c1, oh.c2, oh.c3, oh.c4, c.c1, e.moved-to-payment-history FROM customer c join order-header oh on c.customer-id = oh.customer-id left outer join eft-payment-history e on oh.invoice-number = e.invoice-number WHERE oh.open-amount <> 0 and e.moved-to-payment-history = 'n' and c.customer-id = '12345'
If I use the "old" tSQL *= this select also works ( 6 rows returned )
SELECT oh.c1, oh.c2, oh.c3, oh.c4, c.c1, e.moved-to-payment-history FROM customer c, order-header oh, eft-payment-history e WHERE c.customer-id = oh.customer-id and oh.invoice-number *= e.invoice-number and oh.open-amount <> 0 and e.moved-to_payment-history = 'n' and c.customer-id = '12345'
I've been SLOWLY converting PowerBuilder datawindows from *= to LOJ syntax. What puzzles me is the SQL I expected to work doesn't, and the LOJ that I didn't expect to work does. Explanations from a GURU would be helpful.
Do the 6 rows from the expected result have values for the e.moved-to-payment-history column? Or is this NULL?
In case the latter is true: That would be fully expected behaviour:
Using a filter for a row from the NULL-supplying side of an outer join in the WHERE clause that just accepts TRUE will turn the outer join in an implicit inner join. This is explained in detail in this doc page.
If e.moved-to-payment-history column is not null, well, then I don't have a clue.
answered 11 Sep '12, 03:32
IMO the predicate "e.moved_to_payment_history = 'n'" does not belong in the LEFT OUTER JOIN - ON clause because it doesn't have anything to do with the join.
Instead, consider applying it to the eft_payment_history table before the LEFT OUTER JOIN.
Here are three suggested steps in converting the Transact SQL query to ANSI syntax (Caveat Emptor! ...this code has not been tested):
SELECT oh.c1, oh.c2, oh.c3, oh.c4, c.c1, e.moved_to_payment_history FROM customer c, order_header oh, eft_payment_history e WHERE c.customer_id = oh.customer_id and oh.invoice_number *= e.invoice_number and oh.open_amount <> 0 and e.moved_to_payment_history = 'n' and c.customer_id = '12345'
(1) Move the "c.customer_id = oh.customer_id" to the INNER JOIN - ON clause.
(2) Move the "e.moved_to_payment_history = 'n'" to a derived "( SELECT ... WHERE ... )" table expression so that it is applied to all the actual rows in eft_payment_history before the LEFT OUTER JOIN complicates things.
(3) Move the "oh.invoice_number *= e.invoice_number" to the LEFT OUTER JOIN - ON clause.
SELECT oh.c1, oh.c2, oh.c3, oh.c4, c.c1, e.moved_to_payment_history FROM customer c INNER JOIN order_header oh ON c.customer_id = oh.customer_id LEFT OUTER JOIN ( SELECT * FROM eft_payment_history e WHERE e.moved_to_payment_history = 'n' ) AS e ON oh.invoice_number = e.invoice_number WHERE oh.open_amount <> 0 and c.customer_id = '12345'
Derived tables are very useful to "divide and conquer" complex queries.
PS: use PRE tags to separate code from text,
always put a blank line before the opening PRE tag,
use a backslash before an underscore in ordinary text (not always necessary but it doesn't hurt), and
don't pay too much attention to the "preview" display, just the final version.