Re: SQL question

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

OK - that's what I thought. You don't need to have all the fields 
from both files in your SELECT statement - you could even have fields 
from only one file, based on WHERE clause using fields from the other.

So you must NOT use SELECT * FROM whatever

Please show us your statement - this is easy to do once we know what 
you have tried.

Or use something like

select file1.inv#, file1.invdate, file1.invline, file1.lineamt, 
file2.slstax from file1 join file2 on file1.inv# = file2.inv#

Badaboom!

or
select file1.*, file2.slstax from file1 join file2 on file1.inv# = file2.inv#

Badabing!

Regards
Vern

At 07:56 AM 1/4/2008, you wrote:

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