Chuck,
I expect that statement:
SELECT ILITM, ILLITM, ILMCU
FROM F4111
LEFT JOIN F57217C ON ILDOCO = ISBCT
and ILDCTO = 'II'
means in free word ... add this record from file F57217C where ILDOCO is
equal to ISBCT when ILDCTO is also equal "II', for other records - where
ILDCTO is not equal "II" result from F57217C should be blank/null.
Maybe I'm wrong ...
You are right - without knowing JDE my question is ... hard to say any
answer ... sorry ...
So:
F4111 has fields starts with ILxxx
F55008 has field D4xx
F55009 has field D5xx
F57217C has field ISxx
Pozdrawiam
---------------------------------------------------------
Tomasz Skorza
IT Manager
mobile: +48 606 815 067
deskphone: +48 61 665 79 67
Agri Plus Sp. z o.o.
ul. Marcelinska 92/94
60-324 Poznan
phone: +48 61 665 79 60
fax: +48 61 665 79 61
Numer KRS 0000113478
Sad Rejonowy w Poznaniu, XXI Wydzial/ Gospodarczy Krajowego Rejestru Sadowego
Kapital/ zakl/adowy 235 900 000,00 zl/
---------------------
Please consider the environment before printing this email.
W dniu 2010-03-12 19:00, [1]midrange-l-request@xxxxxxxxxxxx pisze:
message: 2
date: Fri, 12 Mar 2010 09:15:21 -0800
from: CRPence [2]<CRPbottle@xxxxxxxxx>
subject: Re: Why I get diffrent result for SQL
Tomasz Skor?a wrote:
I have following query:
<<SNIP query>>
When I call this statements in interactive SQL session
I get correct results - means all records which suite me.
But when I try call the same statement in SQLRPG program
DECLARE C1 CURSOR FOR <<SNIP query>>
I get only the rows where ILDCTO is equal "II"?
WHY? What is wrong? How to get all rows in second issue?
SELECT ILITM, ILLITM, ILMCU
FROM F4111
LEFT JOIN F57217C ON ILDOCO = ISBCT
and ILDCTO = 'II'
Since there is selection ON ILDCTO = 'II', why would any rows
that do not meet that criteria appear? It would seem that if rows
that do not meet that criteria appear for the STRSQL session, then
there is a problem with the STRSQL results; i.e. perhaps then, not a
problem with the RPG embedded SQL?
FWiW since the reader has no idea which columns come from which
TABLE, it is best to provide correlation identifiers for the files
and specify them as qualifiers on the column references; i.e. make
it easier for others to assist, by providing the valuable details.
Without the necessary details, too many *assumptions* have to be
made about the scenario.
For lack of an ORDER BY, do not [incorrectly] conclude "wrong
rows" are returned if the first block of returned rows are either
different than or do not collate the same, when contrasting the
results between the interactive request and the embedded request.
Such a conclusion is folly, since an SQL result set has no
predictable collation without an explicit ORDER BY to effect ordered
rows [keyed across unique values].
Regards, Chuck
References
Visible links
1. mailto:midrange-l-request@xxxxxxxxxxxx
2. mailto:CRPbottle@xxxxxxxxx
midrange.com
