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

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

John EllisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Pawan KumarDatabase ExpertCommented:
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

0
HainKurtSr. System AnalystCommented:
just get rid of

as REORDERPOINTANALYSIS2

Open in new window


at the end...
0
Pawan KumarDatabase ExpertCommented:
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.
0

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