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

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

Open in new window

John EllisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Check whether you have proper indices:

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

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:

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

Open in new window

John EllisAuthor Commented:
Hi ste5an:

I'm sorry.  But, I don't understand either of your suggestions.  Could you please elaborate?

Thanks!

John
John EllisAuthor Commented:
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

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

Open in new window

Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

ste5anSenior DeveloperCommented:
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.
John EllisAuthor Commented:
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.
John EllisAuthor Commented:
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

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]

Open in new window

HainKurtSr. System AnalystCommented:
Anyway, below is my miniaturized query.  Please help me with my syntax.  Thank you!

looks like this is Jigsaw puzzle :)
you copied and pasted different parts from a big query and put together randomly and ask for help to fix...
I looked at that code for 3 minutes and it did not make any sense to me...

you should start from scratch...
and go slowly and step by step...

select * from tableA

Open in new window


select * from tableA a, tableB b

Open in new window


select * from tableA a inner join tableB b on a.id=b.id

Open in new window


select a.col1, a.col2, b.col1,...
  from tableA a inner join tableB b on a.id=b.id

Open in new window


select a.col1, a.col2, b.col1,...
  from tableA a inner join tableB b on a.id=b.id
 where a.colX in (...) and b.colY = ...
order by ...

Open in new window


etc...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.