August 20, 2009 at 11:33 pm
Hi There, Can someone please tell me why I get this error message: Each GROUP BY expression must contain at least one column that is not an outer reference.
Here is the code:
SELECT d.ITEMNMBR,
d.ITEMDESC,
d.ABCCODE,
d.UOMSCHDL,
Month6 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 6)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
END),
Month5 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 5)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
END),
Month4 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 4)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
END),
Month3 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 3)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
END),
Month2 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 2)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
END),
Month1 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 1)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
END),
CurrentMonth = SUM(CASE
WHEN ((b.ITEMNMBR = d.ITEMNMBR)
AND (b.DOCTYPE IN (1, 6))
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()))
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE())))
THEN (b.TRXQTY) * - 1
END),
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.LOCNCODE = ''
THEN f.QTYONORD
END AS QtyOnOrder,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.SFTYSTCKQTY > 0
THEN f.SFTYSTCKQTY
END AS SafetyStock,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.LOCNCODE = ''
THEN f.QTYONHND
END AS QtyOnHand,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.LOCNCODE = ''
THEN f.PRCHSNGLDTM / 7
END AS Planning_Lead_Time,
e.VENDORID AS Supplier,
DATEPART(MM, GETDATE()) AS This_Month
FROM IV00101 AS d
LEFT OUTER JOIN IV00103 AS e ON d.ITEMNMBR = e.ITEMNMBR
LEFT OUTER JOIN IV30300 AS b ON d.ITEMNMBR = b.ITEMNMBR
LEFT OUTER JOIN IV00102 AS f on d.itemnmbr = f.itemnmbr
WHERE (d.ITEMTYPE <> 2)
GROUP BY d.ITEMNMBR,
d.ITEMDESC,
d.ABCCODE,
d.UOMSCHDL,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.LOCNCODE = ''
THEN f.QTYONORD
END,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.SFTYSTCKQTY > 0
THEN f.SFTYSTCKQTY
END,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.LOCNCODE = ''
THEN f.QTYONHND
END,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.LOCNCODE = ''
THEN f.PRCHSNGLDTM / 7
END,
e.VENDORID,
DATEPART(MM, GETDATE())
August 21, 2009 at 1:44 am
Hi,
i cant test your statement if it's right.
But maybe i can help you to write the sql a little bit shorter and clearer.
You can join the sum's you needet within the from Clause. Then you don't neet the complex where statements within Fiels section.
my sample:
FROM IV00101 AS d
LEFT OUTER JOIN IV00103 AS e ON d.ITEMNMBR = e.ITEMNMBR
LEFT OUTER JOIN IV30300 AS b ON d.ITEMNMBR = b.ITEMNMBR
LEFT OUTER JOIN IV00102 AS f on d.itemnmbr = f.itemnmbr
LEFT OUTER JOIN (SELECT ITEMNMBR,SUM(TRXQTY) * - 1 AS SUMTRXQTY, DATEPART(year, DOCDATE) * 100 + DATEPART(MM, DOCDATE) AS YEARMONTH
FROM IV30300 AS a
WHERE (ITEMNMBR = d.ITEMNMBR)
AND (DOCTYPE IN (1, 6))
AND (TRXSORCE NOT LIKE 'IVADJ%' AND DOCNUMBR NOT LIKE 'RTS%' AND DOCNUMBR NOT LIKE 'ADJ%')
AND (TRXQTY < 0)) itemValue
ON d.ITEMNMBR = itemValue.ITEMNMBR
This code is not testet. It should only be a idea.
Further i changed the function-calls on existing columns. I think you can do this with the more performant Like.
August 21, 2009 at 4:31 am
Is it possible to do this? (Jeff's gonna kill me for this one - RBAR hall of shame I suspect).
select * from (
SELECT
d.ITEMNMBR as ItemNmbr,
d.ITEMDESC as ItemDesc,
d.ABCCODE as ABSCode,
d.UOMSCHDL as UOMSchdl,
Month6 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 6)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
END),
Month5 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 5)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
END),
Month4 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 4)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
END),
Month3 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 3)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
END),
Month2 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 2)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
END),
Month1 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 1)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
END),
CurrentMonth = SUM(CASE
WHEN ((b.ITEMNMBR = d.ITEMNMBR)
AND (b.DOCTYPE IN (1, 6))
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')
AND (b.TRXQTY 0
THEN f.SFTYSTCKQTY
END AS SafetyStock,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.LOCNCODE = ''
THEN f.QTYONHND
END AS QtyOnHand,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.LOCNCODE = ''
THEN f.PRCHSNGLDTM / 7
END AS Planning_Lead_Time,
e.VENDORID AS Supplier,
DATEPART(MM, GETDATE()) AS This_Month
FROM IV00101 AS d
LEFT OUTER JOIN IV00103 AS e ON d.ITEMNMBR = e.ITEMNMBR
LEFT OUTER JOIN IV30300 AS b ON d.ITEMNMBR = b.ITEMNMBR
LEFT OUTER JOIN IV00102 AS f on d.itemnmbr = f.itemnmbr
WHERE (d.ITEMTYPE 2)
) X
group by
X.ItemNmbr,
X.ItemDesc,
X.ABSCode,
X.UOMSchdl,
X.Month6,
X.Month5,
X.Month4,
X.Month3,
X.Month2,
X.Month1,
X.CurrentMonth,
X.QtyOnOrder,
X.SafetyStock,
X.QtyOnHand,
X.Planning_Lead_Time,
X.Supplier,
X.This_Month
August 23, 2009 at 9:03 am
Please see the following...
http://www.sqlservercentral.com/Forums/Topic774023-149-1.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2009 at 5:30 pm
Hi there. Thanks for the help. I tried code very similar to that, but without the "This Month" Column and it worked. But worked slowly. Took 36 seconds. Jeff is right. The code I wrote isn't very good!!
August 24, 2009 at 7:55 am
Just a suggestion. The subquery used now can be put in a temp table or table var that should increase the performance.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
August 24, 2009 at 4:43 pm
I wish I knew how to do that!!
August 25, 2009 at 12:25 am
In place of having the select in the join replace the select part with a temp table here and create the temp table before you run this query. This is just an example of how the syntax work. Please let me know if you are not able to get it right. If not please post you query and I will try and fix the problems.
SELECT ITEMNMBR,SUM(TRXQTY) * - 1 AS SUMTRXQTY, DATEPART(year, DOCDATE) * 100 + DATEPART(MM, DOCDATE) AS YEARMONTH
into #temp
FROM IV30300 AS a
WHERE (ITEMNMBR = d.ITEMNMBR)
AND (DOCTYPE IN (1, 6))
AND (TRXSORCE NOT LIKE 'IVADJ%' AND DOCNUMBR NOT LIKE 'RTS%' AND DOCNUMBR NOT LIKE 'ADJ%')
AND (TRXQTY < 0)
FROM IV00101 AS d
LEFT OUTER JOIN IV00103 AS e ON d.ITEMNMBR = e.ITEMNMBR
LEFT OUTER JOIN IV30300 AS b ON d.ITEMNMBR = b.ITEMNMBR
LEFT OUTER JOIN IV00102 AS f on d.itemnmbr = f.itemnmbr
LEFT OUTER JOIN #temp itemValue
ON d.ITEMNMBR = itemValue.ITEMNMBR
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply