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