RE: SQL question

Similarly, you could do

Select d.*, h.*
From detail d left outer join header h
On d.invnbr = h.invnbr and d.linnbr = 1

But again, not the way I would do it.

Charles

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Michael_Schutte@xxxxxxxxxxxx
Sent: Friday, January 04, 2008 10:22 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL question

This actually gave me an idea... although, personally I don't like....

Select a.*, case when a.LineNum = 1
then (select salestax from file2 b where a.order = b.order)
else 0
End
from file1




Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
"The Secret's the Sauce! Enjoy our new Bob-B-Q Pulled Pork Knife & Fork
Sandwich!"




"Doug Davie"
<dougdavie@gmail.
com> To
Sent by: "RPG programming on the AS400 /
rpg400-l-bounces@ iSeries" <rpg400-l@xxxxxxxxxxxx>
midrange.com cc

Subject
01/04/2008 10:16 Re: SQL question
AM


Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>






select a1.INVNUM, a1.INVDATE, a1.INVLINE, a1.INVAMT,
ifnull((select b1.TAXNUM from TAX as b1 where a1.INVNUM = b1.TAXNUM
and a1.INVLINE =
(select min(c1.INVLINE) from INV as c1 where c1.INVNUM = a1.INVNUM)),0) as
tax
from INV as a1
order by a1.INVNUM desc, a1.INVLINE
--
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.



--
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.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.