RE: SQL question -- RPG400-L

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

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

What happens is that the sales tax is coming from the second file as the
total actual amount for the invoice, but it should only show the first
time so that you can get a true total of sales tax. by joining
conventionally it does bring the 21.00 to each joined line which is not
what is wanted.

Invoice #, Date, Inv.Line Invoice line Amt, Sales Tax
1234 010108 01 100.00
21.00
1234 010108 02 200.00
21.00
1234 010108 03 99.00
21.00

vhamberg@xxxxxxxxxxx wrote:
Maybe so - took only a cursory glance.

An option might be to use a Query Management form to get the summary lines
- not sure. It's not clear what he really meant!
-------------- Original message ----------------------
From: Michael_Schutte@xxxxxxxxxxxx

Vern,

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
Item 2 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

To do this, doing a join wouldn't be the best in my opinion... because
you'll be accessing the sales tax file multiple times when it should

only

be done once when the order number changes. I believe that's what he was
asking to be able to do with SQL but it cannot be done.

Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
3776 South High Street, Columbus, OH 43207
Phone: 614/492-7419 Fax: 614/409-2017
Visit us on the web! www.bobevans.com

(Embedded image moved to file: pic17410.gif)

"The Secret's the Sauce! Enjoy our new Bob-B-Q Pulled Pork Knife & Fork
Sandwich!"

rpg400-l-bounces@xxxxxxxxxxxx wrote on 01/03/2008 04:48:32 PM:

You just have to be sure not to use the

select *

syntax - list the individual fields/columns instead. If the names
are the same in each file, qualify them with the file name or and
alias name for the file.

Even in Query/400 you could do field selection, which'd accomplish
the same thing.

HTH
Vern

-------------- Original message ----------------------
From: Michael_Schutte@xxxxxxxxxxxx

If I understand correctly... you are not going to be able to do what

you

want...

When you do a join, the secondary file will get joined to the

primary

file

where matches occur. So the sales tax you talk of will be joined to

every

record. IMO, you should change your logic. First to get the sales

tax

for the whole order, then separately process the primary file you

speak

of.

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

Fork

Sandwich!"

rpg400-l-bounces@xxxxxxxxxxxx wrote on 01/03/2008 02:51:41 PM:

HI I have 2 files that are to be joined. in the secondary file

there

is a field that is used in the report but I only want to show it

one

time. To clarify, I am showing the invoice number and other

details,

while the sales tax field is in the second file. I only wish to
show this sales tax once per order for the report and as wel to
tally this sales tax field as well as other fields.

I am unsure how to do this in SQL. In query the sales tax will
show each time.

I can always write an RPG program to do this but was looking for
the quicker method.

Thanks,

---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile.
Try it now.
--
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 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.

---------------------------------
Never miss a thing. Make Yahoo your homepage.
--
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.