RE: SQL question

If the tax is defined and populated at the detail level, my first assumption would be that the value desired at the header level will be a SUM() of the detail.

If so, there is no way around a sub-select. because a summary value is desired from the same detail file that is needed for detail lines in the report.

Assuming header file HHH and detail file DDD...

WITH HH2 ( ORDNBR, ORDTOTAL, ORDTAX )
AS ( SELECT HHH.ORDNBR, SUM( DDD.ITEMAMOUNT ), SUM( DDD.ITEMTAX )
FROM HHH INNER JOIN DDD ON HHH,.ORDNBR = DDD.ORDNBR
GROUP BY HHH.ORDNBR )
SELECT HH2.ORDNBR, HH2.ORDTOTAL, HH2.ORDTAX, DDD.ItemNumber, DDD.ItemAmount, DDD.ItemTax
FROM HH2 JOIN DDD ON HH2.ORDNBR = DDD.ORDNBR
ORDER BY HH2.ORDNBR, DDD.ItemNumber

From what I understand he wants to produce a report...

Order 111 Total 105.34 Tax 13.25
Item 1 Total 1.25 Tax .06
(
...
Order 112 Total 175.34 Tax 18.35
Item 1 Total 1.25 Tax .06
Item 2 Total 1.25 Tax .06
...

Report Total 280.78 Tax 31.60





-------------- Original message ----------------------
From: "Wilt, Charles" <WiltC@xxxxxxxxxx>

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