Re: another sql question

["Tony Carolla" <carolla@xxxxxxxxx>]

Assuming the query works as is, it sounds like you want the following:

SELECT     A.pdvnno, A.pdcono, A.pdorid, A.pdlnsq, A.pditno, A.pdlnst,
          A.pdorqt, A.pdqrcd, B.tdtqrc, B.tdstas,
          A.pdorqt - A.pdqrcd AS outstng, Z.grdesc
FROM aplus2ftx/podet A
LEFT JOIN     aplus2ftx/pordt B ON A.pdorid = B.tdpono
          AND A.pdcono = B.tdcono AND A.pdlnsq = B.tdlnsq
          AND A.pditno = B.tditno and B.tdstas <> 'C'
LEFT JOIN     aplus2ftx/itmst I ON A.pditno = I.imitno
LEFT JOIN     aplus2ftx/zgrpdscp Z ON I.imitcl = Z.grcode
WHERE     A.pdlnst = 'O'
ORDER BY 2,1

If you are looking for a good book, I have had great experiences with the
Head First series of books.  Head First SQL is a great book to give you a
solid foundation in SQL, and it is written in a very interesting way, so as
to teach in a real-world way.




On 10/31/07, Rowe, Sheri <srowe@xxxxxxxxx> wrote:
> Hi Group,
>
> I have an sql statement, which this group helped me with, and is working
> great, but now my boss wants another field, containing a description,
> entered in the resulting file and I can't figure out how to do it with
> SQL.  To get this field I need to add two files not in this query, ITMST
> and ZGRPDSCP.
>
> SELECT a.pdvnno, a.pdcono, a.pdorid, a.pdlnsq, a.pditno, a.pdlnst,
> a.pdorqt, a.pdqrcd, b.tdtqrc, b.tdstas, A.PDORQT - A.PDQRCD as
> OutStng
> FROM aplus2ftx/podet A left outer join aplus2ftx/pordt B on
> a.pdorid = b.tdpono and a.pdcono = b.tdcono and a.pdlnsq = b.tdlnsq
> and a.pditno = b.tditno and b.tdstas <> 'C'
> WHERE a.pdlnst = 'O'
> ORDER BY 2,1
>
> File ITMST, has item # (imitno) and Item class (imitcl) fields.
> File ZGRPDSCP has item class # (grcode) and Item class description
> (grdesc) fields.
>
> I need to join podet.pditno to itmst.imitno to get the imitcl value,
> then join itmst.imitcl to zgrpdscp.grcode to get the grdesc field.
>
> Thanks for your help.
>
> Sheri
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.