T-SQL: No Data is Being Returned

John Ellis
John Ellis used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

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

Author

Commented:
Hi ste5an:

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

Thanks!

John

Author

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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer

Commented:
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.

Author

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.

Author

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

Sr. System Analyst
Commented:
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...
ste5anSenior Developer

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial