Software Engineer
asked on
T-SQL: No Data is Being Returned
Hello:
Below is my T-SQL query. I have no results, to show, as this query is taking too long to run.
Can someone please review the syntax of my Recent Consumption and Average Consumption fields and tell me if I'm doing anything wrong there?
The following is the synopsis of what data these two fields are intended to display:
Recent Consumption - Sum of all Inventory Adjustments for non 70- and Sales Invoices for 70- items for the past 3 months
Average Consumption - Sum of all Inventory Adjustments for non 70- and Sales Invoices for 70- for the past 12 months divided by 4
FYI – This all means items that are prefixed by the designation “70-“.
Even if the amount in each of these two fields is 0, data should be displaying showing all items at IV00102.LOCNCODE (i.e. all items located at site 70).
Thank you!
John
Below is my T-SQL query. I have no results, to show, as this query is taking too long to run.
Can someone please review the syntax of my Recent Consumption and Average Consumption fields and tell me if I'm doing anything wrong there?
The following is the synopsis of what data these two fields are intended to display:
Recent Consumption - Sum of all Inventory Adjustments for non 70- and Sales Invoices for 70- items for the past 3 months
Average Consumption - Sum of all Inventory Adjustments for non 70- and Sales Invoices for 70- for the past 12 months divided by 4
FYI – This all means items that are prefixed by the designation “70-“.
Even if the amount in each of these two fields is 0, data should be displaying showing all items at IV00102.LOCNCODE (i.e. all items located at site 70).
Thank you!
John
select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code],
IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],
IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],
CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''
WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'
WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'
WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy'
END as [Replenishment Method],
CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'
WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'
WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'
WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'
WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'
WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'
END as [Order Policy],
IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],
COALESCE(CASE WHEN LEFT(IV00102.ITEMNMBR, 3) <> '70-' THEN (select SUM(IV30300.EXTDCOST) where IV30300.DOCDATE > DATEADD(m,-3,getdate())) ELSE
(select SUM(SOP30300.XTNDPRCE) where SOP30200.DOCDATE > DATEADD(m,-3,getdate()))
END, 0) as [Recent Consumption],
(COALESCE(CASE WHEN LEFT(IV00102.ITEMNMBR, 3) <> '70-' THEN (select SUM(IV30300.EXTDCOST) where IV30300.DOCDATE > DATEADD(m,-12,getdate())) ELSE
(select SUM(SOP30300.XTNDPRCE) where SOP30200.DOCDATE > DATEADD(m,-12,getdate()))
END, 0))/4 as [Average Consumption],
IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],
IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]
from IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT OUTER JOIN IV30300
ON IV00102.ITEMNMBR = IV30300.ITEMNMBR
LEFT OUTER JOIN SOP30300
ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR
INNER JOIN SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6)
GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,
IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV30300.DOCDATE, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCD, SOP30200.DOCDATE
ASKER
Hi ste5an:
I'm sorry. But, I don't understand either of your suggestions. Could you please elaborate?
Thanks!
John
I'm sorry. But, I don't understand either of your suggestions. Could you please elaborate?
Thanks!
John
ASKER
Hello:
With my revised code below, I believe that I'm on the right track. But, I get the following error:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Any thoughts?
Thanks!
John
With my revised code below, I believe that I'm on the right track. But, I get the following error:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Any thoughts?
Thanks!
John
select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code],
IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],
IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],
CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''
WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'
WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'
WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy'
END as [Replenishment Method],
CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'
WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'
WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'
WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'
WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'
WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'
END as [Order Policy],
IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],
SUM(CASE WHEN LEFT(IV00102.ITEMNMBR, 3) <> '70-' THEN (select (IV30300.EXTDCOST) where IV30300.DOCDATE > DATEADD(m,-3,getdate())) ELSE
(select (SOP30300.XTNDPRCE) where SOP30200.DOCDATE > DATEADD(m,-3,getdate()))
END) as [Recent Consumption],
SUM(CASE WHEN LEFT(IV00102.ITEMNMBR, 3) <> '70-' THEN (select SUM(IV30300.EXTDCOST) where IV30300.DOCDATE > DATEADD(m,-12,getdate())) ELSE
(select SUM(SOP30300.XTNDPRCE) where SOP30200.DOCDATE > DATEADD(m,-12,getdate()))
END)/4 as [Average Consumption],
IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],
IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]
from IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT OUTER JOIN IV30300
ON IV00102.ITEMNMBR = IV30300.ITEMNMBR
LEFT OUTER JOIN SOP30300
ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR
INNER JOIN SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6)
GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,
IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCD--, SOP30200.DOCDATE, IV30300.DOCDATE
as this query is taking too long to run
The performance of a query depends on the indices defined on the used table. Consider using the indix in a phone books instead of scanning page by page for a certain name.
A relational database engine works the same.
So check the indices already defined on each used table. This includes the primary key and any index or unique constraint.
The more supporting indices exist, the faster can a query be executed.
The information we need here is the actual execution plan. Activate it under Query/Include Active Execution plan. Save the exection plan and post it.
ASKER
Really, I think that I'm better off if we focus on the code that I just posted and why I'm getting that error.
ASKER
Okay. I have whittled down my query to only one of these two fields, so that someone out there can help me microscopically study this.
The whole issue has to do with that whole blame T-SQL correlated subquery business that only people along the caliber of Albert Einstein, Stephen Hawking, and Sheldon Cooper can understand. I don't know how they expect consultants to conduct work, when they make the programming so hard.
Anyway, below is my miniaturized query. Please help me with my syntax. Thank you!
John
The whole issue has to do with that whole blame T-SQL correlated subquery business that only people along the caliber of Albert Einstein, Stephen Hawking, and Sheldon Cooper can understand. I don't know how they expect consultants to conduct work, when they make the programming so hard.
Anyway, below is my miniaturized query. Please help me with my syntax. Thank you!
John
select
SUM(CASE WHEN LEFT(IV00102.ITEMNMBR, 3) <> '70-' THEN IV30300.EXTDCOST
INNER JOIN IV30300
ON IV00102.ITEMNMBR = IV30300.ITEMNMBR
where IV30300.DOCDATE > DATEADD(m,-3,getdate()) and IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6)
ELSE
SOP30300.XTNDPRCE
INNER JOIN SOP30300
ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR
INNER JOIN SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
where SOP30200.DOCDATE > DATEADD(m,-3,getdate())) and IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6)
END
as [Recent Consumption]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You didn't post anything about an error in your OP.
Below is my T-SQL query. I have no results, to show, as this query is taking too long to run.
Open in new window
Any column in the JOIN predicates should be in an index.
Further more any column in the WHERE and GROUP BY should be covered also:
Open in new window