Re: Why I get diffrent result for SQL

Vern,

You are not right - but this is again my fault - I didn't explain exactly what I would like to see - and what is the base of my issue.
So:
In F4111 I have all records,
In F55008 and F55009 I have JDE date translated to DMY format.
In F57217C I have some ... detailed records for lines with ILDCTO = "II" in F4111

My query have to show all lines from F4111 (with conditions

ILTRQT<>0 AND ILITM<>0 AND ILTRDJ<>0)

with additional fields for JDE date conversion (with conditions

ILDGL=D4DGJ and ILTRDJ=D5DGJ)

and last field IS$FM# which have to get result from F57217C if ILDCTO from F4111 has value "II", for any other lines this last field should be blank on a result page.

Pozdrawiam

---------------------------------------------------------
Tomasz SkorÅa



W dniu 2010-03-12 19:56, midrange-l-request@xxxxxxxxxxxx pisze:

message: 5
date: Fri, 12 Mar 2010 12:54:55 -0600
from: Vern Hamberg<vhamberg@xxxxxxxxxxx>
subject: Re: Why I get diffrent result for SQL

Tomasz

I have found that it is best NOT to put selection criteria in a JOIN
clause - results can be unpredictable, in my experience.

I recommend using a correlated table expression - this is a WITH tename
AS (SELECT ...). then use tename in the final SELECT instead.

You have selection on ILDCTO in the first JOIN - instead, try this (I
put empty lines around the changed line).

WITH F4111TE AS (SELECT * FROM F4111 WHERE ILDCTO = 'II')
SELECT ILITM, ILLITM, ILMCU,
ILLOTN, ILLOCN, ILDOC, ILDCT, ILDGL,
D4DATE, D4DAY, D4WEK, D4MTH, D4YER, ILGLPT, ILDOCO,
ILDCTO, ILTRDJ, D5DATE, D5WEK, D5DAY, D5MTH, D5YER,
ILTRUM, ILAN8,
ILTRQT/getumconv(ILITM, ILTRUM, IMUOM1),
ILPAID/100, IS$FM#

FROM F4111TE LEFT JOIN F57217C

ON ILDOCO = ISBCT
LEFT JOIN F55008 on ILDGL=D4DGJ
LEFT JOIN F55009 on ILTRDJ=D5DGJ
LEFT JOIN F4101 on ILITM=IMITM
WHERE
ILTRQT<>0 AND ILITM<>0 AND ILTRDJ<>0


You might be able to add

ILTRQT<>0 AND ILITM<>0 AND ILTRDJ<>0


to the F4111TE table expression, too. In the statement you have, they
might not be checked until the results of the JOINs has been completed.
If you put them in a table expression, they are more likely to be
evaluated early, and that makes the whole thing run faster. That would
look like this -

WITH F4111TE AS (SELECT * FROM F4111 WHERE ILDCTO = 'II' AND
ILTRQT<>0 AND ILITM<>0 AND ILTRDJ<>0
SELECT ILITM, ILLITM, ILMCU,
ILLOTN, ILLOCN, ILDOC, ILDCT, ILDGL,
D4DATE, D4DAY, D4WEK, D4MTH, D4YER, ILGLPT, ILDOCO,
ILDCTO, ILTRDJ, D5DATE, D5WEK, D5DAY, D5MTH, D5YER,
ILTRUM, ILAN8,
ILTRQT/getumconv(ILITM, ILTRUM, IMUOM1),
ILPAID/100, IS$FM#

FROM F4111TE LEFT JOIN F57217C

ON ILDOCO = ISBCT
LEFT JOIN F55008 on ILDGL=D4DGJ
LEFT JOIN F55009 on ILTRDJ=D5DGJ
LEFT JOIN F4101 on ILITM=IMITM


HTH
Vern