RE: SQL question -- RPG400-L

Another option, without break text.....

Column Heading Usage
INVNBR INVNBR BREAK1
INVDATE INVDATE BREAK1
SALESTAX SALESTAX BREAK1
LINE LINE
LINEAMT LINEAMT SUM

---output----

INVNBR INVDATE SALESTAX LINE LINEAMT
-------------- ---------- ------------- -------------- -------------
1,234 01/04/2008 21.00 1 100.00
2 200.00
3 99.50
-------------
* 399.50

=============
399.50

HTH,

Charles

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Wilt, Charles
Sent: Friday, January 04, 2008 9:30 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL question

That's the way a join works.

There's an important difference between SQL and a Report writer. SQL is
not a report writer, though
most report writers are SQL based. SQL simply returns a given results
set. A report writer then
takes that results set and does additional processing to output the
results that you want to see.

What you need to do in your report writer is suppress the sales tax column
at the detail level and
instead show it as part of a level header or footer.

Query manager can do this, not as nicely as you might want, but it's
decent.

-----------SQL----------------------
SELECT
A.INVNBR, A.INVDATE, A.SALESTAX, B.LINE, B.LINEAMT
FROM "CTSC_W"/"TSTHDR" A,
"CTSC_W"/"TSTDTL" B
WHERE (A.INVNBR = B.INVNBR)
ORDER BY A.INVNBR, B.LINE

-----------Edit Column Format--------------------------

Edit Column Formatting

Type information, press Enter.
For Usage and Edit, press F4 for list.
For Heading, press F4 for prompt.

Column Heading Usage Edit Seq
INVNBR INVNBR BREAK1 1
INVDATE INVDATE 2
SALESTAX SALESTAX OMIT 3
LINE LINE 4
LINEAMT LINEAMT SUM 5

------------Edit Break text----------------------------

Edit Break Footing

Break level . . . . . . . . . : 1

Type choices, press Enter.
Put on new page . . . . . . . . . . . . . N
Blank lines before . . . . . . . . . . . . 0
Blank lines after . . . . . . . . . . . . 1
Put break summary at line . . . . . . . . 1

Break text: Use &col to cause variable insertion.

Line Align Break Footing Text
1 RIGHT Sales Tax: &3 Invoice Total:

-------------Output---------------------------------

INVNBR INVDATE LINE LINEAMT
-------------- ---------- -------------- -----------------
1,234 01/04/2008 1 100.00
01/04/2008 2 200.00
01/04/2008 3 99.50
-----------------
Sales Tax: 21.00 Invoice Total: 399.50

=================
399.50

HTH,

Charles Wilt

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.