Re: another sql question

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.