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 e.moved-to-payment-history is part of the left outer join but e.moved-to-payment-history does NOT exist in the oh table.

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 e.moved-to-payment-history is in the WHERE

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.

asked 10 Sep '12, 18:11

Tom%20Mangano's gravatar image

Tom Mangano
672242738
accept rate: 8%

edited 10 Sep '12, 18:27

Graeme%20Perrow's gravatar image

Graeme Perrow
9.6k379124

FYI code should be indented by 4 spaces - that will make it much easier to read. I've done this for you.

(10 Sep '12, 18:27) Graeme Perrow

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.

permanent link

answered 11 Sep '12, 03:32

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

The 6 returned rows have nulls as expected. The Sybase doc explained the outer join logic.
Thanks for pointing me in the right direction.

(11 Sep '12, 05:10) Tom Mangano
Replies hidden

Glad you got it working! If this has helped, feel free to accept the answer - cf. this FAQ:)

(11 Sep '12, 05:56) Volker Barth

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.

permanent link

answered 11 Sep '12, 08:48

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 11 Sep '12, 08:53

A good suggestion - though in this case, personally, I would think the original posted LEFT JOIN query is more readable - derived queries are great tools, but still not that common, methinks.

(In the end, unless one is aware of this "How an outer join can become an inner join by means of a WHERE clause" issue, both queries would look more difficult than necessary, and the second posted query would be the expected one...)

(11 Sep '12, 09:43) Volker Barth
Replies hidden

I would disagree Volker. Derived tables are exceedingly common with outer joins; they offer a great deal more readability when you want to perform an outer join with more than one table, or when there are a number of conditions on the null-supplying table that would appear on the ON condition.

Breck is right.

(11 Sep '12, 11:16) Glenn Paulley

Well, who am I to debate with you on such topics...

That being said, if derived queries are more common than I'm aware of, that's good news. (So Breck, there have to be more "Alphas/Betas"...) Please note, my remark above is explicitly made for that particular query ("in this case") - simply by comparing both queries. As stated, personally, I can surely read and understand the original query more easily.

For outer joins with more tables and/or conditions, I fully agree with both of you.

(11 Sep '12, 11:32) Volker Barth

@Glenn: BTW and off-topic): What about a fresh debate on surrogate identifiers? Do you have any suggestions for using SA12's sequences to generate easily to discriminate identifiers, such as one

might have a customer identifier such XYZ456, a product code of 7877-876, and a supplier identifier of 879BC

? I'd be interested:)

(11 Sep '12, 11:42) Volker Barth
1

re: "How an outer join can become an inner join by means of a WHERE clause"... that was the first SQL question I ever asked online, way back in the last millennium, in the Land Of 1200 Baud, and it was answered by none other than Joe Celko.

(11 Sep '12, 15:33) Breck Carter

Derived queries are very common... old folks call them "views" and jump through hoops to code CREATE statements... kids today are lazy, and code them in the FROM clause :)

(11 Sep '12, 15:39) Breck Carter
showing 1 of 6 show all flat view

Long ago I wrote a whitepaper on TSQL outer join semantics, which includes ISO SQL-standard equivalents. You may find it helpful.

permanent link

answered 11 Sep '12, 07:36

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

Thank you for the white paper.
I'm passing it on to the other programmers I work with.

(11 Sep '12, 08:50) Tom Mangano
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25
×7

question asked: 10 Sep '12, 18:11

question was seen: 4,298 times

last updated: 11 Sep '12, 15:39