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



Thanks to all who responded.
I have redesigned my transaction file to add a Purchase/Donated flag and changed my query to join using this value.
The entire process now runs in under a minuite to process 700 transactions.
It was taking over an hour before.


 
Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3
  
 





----- Original Message ----
From: "Rick.Chevalier@xxxxxxxxxxxxxxx" <Rick.Chevalier@xxxxxxxxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Sent: Thursday, October 30, 2008 2:41:40 PM
Subject: RE: Help with complex SQL statement


Jeff,

Can you help me break it down into it's component parts?

What I think I know:
- You receive customer information in a summarized format.
- You need to compare your itemized file with the customer summarized file.
- If an item is marked as donated but the customer file doesn't show a donated quantity include it.
- If an item is marked as purchased but the customer file doesn't show a purchase quantity include it.

Based on the above assumptions what about going in the opposite direction and summarizing your files to match the customer file?  This would eliminate the two CTE's.  You might even be able to do the summarization at the same time you join to the customer file if a quantity match isn't important.  That would eliminate the two sub-selects in your first where clause.

I haven't gotten to the queries past the union yet.

Rick

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Thursday, October 30, 2008 1:03 PM
To: midrange-l@xxxxxxxxxxxx; rpg400-l@xxxxxxxxxxxx
Subject: Help with complex SQL statement

I have a complex SQL statement that is taking a very long time to execute.
This statement takes about 30 - 45 seconds, and is executed about 700 times over the duration of the program.

What I am attempting to do is to determine if I have any unmatched transactions from my invoice detail file to a customer supplied invoice payment file.
What makes it complicated is that the same customer item number can refer to two different items in my invoice file based on whether the purchased quantity or donated quantity was used.
The customer supplies me with an invoice number, their item number, the purchased qty and the donated quantity for each item on the invoice.  In addition, they will summarize the quantity if the item appears multiple times on the invoice or if there is a credit with the same invoice number.

The files that I am using are:
VASTRAND - Customer transactions
Invoice
Customer item
:Purchase Qty
Donated Qty

For file VASTRAND, there is an index over the customer item number field.
In addition, I have a separate view for purchased items and for donated items.

VSADETL - Invoice Details
Invoice
Customer Item
Invoice Type
Customer Item Numer
Our Item Number

FINITEM - Item Master
Our Item Number
Donated Flag

If the customer transaction has donated quantity, it must match a donated item for the same customer item on my invoice.  If it is purchased item, it must match a purchased item on the invoice.

The SQL I am using is:
With Purchased as (    SELECT Distinct ASCSIT FROM VAsTranD
Where    AsPQty <> 0 and AsInv = ?), Donated as(    SELECT
Distinct ASCSIT FROM VAsTranD Where    AsDQty <> 0 and AsInv = ?)    Select SbItem,SbCsIt,    Sum(Case SbType When 'O'
Then SbQShp Else (SbQShp * -1) End) SbQShp,    Min(SbPric)
SbPrice,Min(FIcDonated) FIcDonated from VSADETL    Join FInItem on SBItem = FICItem Where    ((FIcDonated = 'Y' and SbCsIt Not In (Select AsCsIt From Donated))    Or (FIcDonated
= 'N' and SbCsIt Not In (Select AsCsIt From Purchased)))
  and SbDel = 'A'and Sbinv = ?    Group by SbItem,SbCsIt
Union All    Select SbItem,SbCsIt,    Sum(Case SbType When
'O' Then SbQShp Else (SbQShp * -1) End) SbQShp,    Min(SbPric) SbPrice,Min(FIcDonated) FIcDonated from VSADETL    Join
FInItem on SbItem = FIcItem    Exception Join VAsTranD on
SbInv = AsInv and SbCsIt = AsCsIt    Where SbDel = 'A' and
SbInv = ?    Group by SbCSit,SbItem    Order by SbItem,SbCsIt

Any suggestions for improving this will be appreciated.

Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 IBM  Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3 IBM  Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.



Privileged and Confidential.  This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information.  If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail.  You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


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.