Why I get diffrent result for SQL

[Tomasz SkorÅa <t.skorza@xxxxxxxxxxx>]

   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