× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.