T-SQL: Incorrect Syntax Near the Keyword 'as'

John Ellis
John Ellis used Ask the Experts™
on
Hello:

Sigh....for whatever reason, whenever I try to do "Select Top 100 Percent * from..." in T-SQL, I can never get the placement of the ending parentheses correct.

Please review my query below to let me know where my syntax is wrong.

Thank you!

John

Select [Item Number], [Item Description], [Location Code], [Base U of M], [QTY On Hand], [QTY On Order], [QTY Available], [Replenishment Method], [Order Policy], [Fixed Order Qty],
[Order Point Qty], [Order Up To Level], SUM([Recent Consumption]), SUM([Average Consumption]), [Buyer ID], [Planner ID], [PRODUCT LINE], [Item Class Code] from 
(
select TOP 100 PERCENT * from (
--not 70; inventory adjustments; 3 months (Recent Consumption)
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(SUM(IV30300.EXTDCOST), 0) as [Recent Consumption],
0 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 
where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and IV30300.DOCDATE > DATEADD(m,-3,getdate())
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
UNION
--not 70; inventory adjustments; 12 months/4 (Average Consumption)
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],
0 as [Recent Consumption],
COALESCE(SUM(IV30300.EXTDCOST), 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 
where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and IV30300.DOCDATE > DATEADD(m,-12,getdate())
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
UNION
--70; inventory adjustments; 3 months (Recent Consumption)
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(SUM(IV30300.EXTDCOST), 0) as [Recent Consumption],
0 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 
where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and IV30300.DOCDATE > DATEADD(m,-3,getdate())
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
UNION
--70; inventory adjustments; 12 months/4 (Average Consumption)
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],
0 as [Recent Consumption],
COALESCE(SUM(IV30300.EXTDCOST), 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 
where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and IV30300.DOCDATE > DATEADD(m,-12,getdate())
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
UNION
--not 70; not inventory adjustments (sales); 3 months (Recent Consumption)
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(SUM(SOP30300.XTNDPRCE), 0) as [Recent Consumption],
0 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 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) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and SOP30200.DOCDATE > DATEADD(m,-3,getdate())
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
UNION
--not 70; not inventory adjustments (sales); 12 months/4 (Average Consumption)
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],
0 as [Recent Consumption],
COALESCE(SUM(SOP30300.XTNDPRCE), 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 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) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and SOP30200.DOCDATE > DATEADD(m,-12,getdate())
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
UNION
--70; not inventory adjustments (sales); 3 months (Recent Consumption)
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(SUM(SOP30300.XTNDPRCE), 0) as [Recent Consumption],
0 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 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) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and SOP30200.DOCDATE > DATEADD(m,-3,getdate())
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
UNION
--70; not inventory adjustments (sales); 12 months/4 (Average Consumption)
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],
0 as [Recent Consumption],
COALESCE(SUM(SOP30300.XTNDPRCE), 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 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) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and SOP30200.DOCDATE > DATEADD(m,-12,getdate())
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)
as DATA) as REORDERPOINTANALYSIS
GROUP BY [Item Number], [Item Description], [Location Code], [Base U of M], [QTY On Hand], [QTY On Order], [QTY Available], [Replenishment Method], [Order Policy], [Fixed Order Qty],
[Order Point Qty], [Order Up To Level], [Buyer ID], [Planner ID], [PRODUCT LINE], [Item Class Code]
as REORDERPOINTANALYSIS2

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this

Column name as col1 --- can use used in SELECT list to change the output . You cannot use that in Group by 

Select [Item Number], [Item Description], [Location Code], [Base U of M], [QTY On Hand], [QTY On Order], [QTY Available], [Replenishment Method], [Order Policy], [Fixed Order Qty],
[Order Point Qty], [Order Up To Level], SUM([Recent Consumption]), SUM([Average Consumption]), [Buyer ID], [Planner ID], [PRODUCT LINE], [Item Class Code] from 
(
select TOP 100 PERCENT * from (
--not 70; inventory adjustments; 3 months (Recent Consumption)
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(SUM(IV30300.EXTDCOST), 0) as [Recent Consumption],
0 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 
where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and IV30300.DOCDATE > DATEADD(m,-3,getdate())
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
UNION
--not 70; inventory adjustments; 12 months/4 (Average Consumption)
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],
0 as [Recent Consumption],
COALESCE(SUM(IV30300.EXTDCOST), 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 
where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and IV30300.DOCDATE > DATEADD(m,-12,getdate())
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
UNION
--70; inventory adjustments; 3 months (Recent Consumption)
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(SUM(IV30300.EXTDCOST), 0) as [Recent Consumption],
0 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 
where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and IV30300.DOCDATE > DATEADD(m,-3,getdate())
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
UNION
--70; inventory adjustments; 12 months/4 (Average Consumption)
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],
0 as [Recent Consumption],
COALESCE(SUM(IV30300.EXTDCOST), 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 
where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and IV30300.DOCDATE > DATEADD(m,-12,getdate())
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
UNION
--not 70; not inventory adjustments (sales); 3 months (Recent Consumption)
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(SUM(SOP30300.XTNDPRCE), 0) as [Recent Consumption],
0 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 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) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and SOP30200.DOCDATE > DATEADD(m,-3,getdate())
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
UNION
--not 70; not inventory adjustments (sales); 12 months/4 (Average Consumption)
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],
0 as [Recent Consumption],
COALESCE(SUM(SOP30300.XTNDPRCE), 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 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) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and SOP30200.DOCDATE > DATEADD(m,-12,getdate())
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
UNION
--70; not inventory adjustments (sales); 3 months (Recent Consumption)
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(SUM(SOP30300.XTNDPRCE), 0) as [Recent Consumption],
0 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 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) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and SOP30200.DOCDATE > DATEADD(m,-3,getdate())
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
UNION
--70; not inventory adjustments (sales); 12 months/4 (Average Consumption)
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],
0 as [Recent Consumption],
COALESCE(SUM(SOP30300.XTNDPRCE), 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 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) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and SOP30200.DOCDATE > DATEADD(m,-12,getdate())
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)
as DATA) as REORDERPOINTANALYSIS
GROUP BY [Item Number], [Item Description], [Location Code], [Base U of M], [QTY On Hand], [QTY On Order], [QTY Available], [Replenishment Method], [Order Policy], [Fixed Order Qty],
[Order Point Qty], [Order Up To Level], [Buyer ID], [Planner ID], [PRODUCT LINE], [Item Class Code]

Open in new window

HainKurtSr. System Analyst

Commented:
just get rid of

as REORDERPOINTANALYSIS2

Open in new window


at the end...
Database Expert
Awarded 2016
Top Expert 2016
Commented:
When we group by we have to provide the column names. We cannot change column name in group by clause.

using as keyword we change the name of the column in the select list , so in group by it is not allowed.

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