; with cte as (SELECT CI.TEAM AS ProgramMgr, OH.OH_CUKEY AS Customer, MOC.MOC_PART_NO AS Item, IM.IM_REV AS Rev, OH.OH_CUST_PO_NUM AS CustPONum, OH.OH_NUMBER AS SPNum, MOC.MOC_REF_NO AS SOLine, MOC.MOC_USER_11 AS OrigBlanketQty, ISNULL(v_BlanketSODeliveryQtyByLine.BookedQty, 0)AS QtyOrdered, MOC_USER_11 - ISNULL(v_BlanketSODeliveryQtyByLine.BookedQty, 0) AS RemainingBlanketQty, CASE WHEN MOC_USER_21 = '1901-01-01' THEN NULL ELSE MOC_USER_21 END AS BlanketExpDate, MOC.MOC_PRICE AS LinePrice, RQ.RQ_DATE_REQ AS RequiredDate, RQ.RQ_DATE_PROM AS PromiseDate, RQ.RQ_QTY_REQ AS RequiredQty, RQ.RQ_QTY_ISS AS ShippedQty, CASE WHEN RQ_STATUS = 'C' OR RQ_QTY_ISS > RQ_QTY_REQ THEN 0 ELSE RQ_QTY_REQ - RQ_QTY_ISS END AS OpenQty FROM OH INNER JOIN MOC ON OH.OH_NUMBER = MOC.MOC_OHNUMBER LEFT JOIN RQ ON (MOC.MOC_OHNUMBER = RQ.RQ_MOCNUMBER) AND (MOC.MOC_REF_NO = RQ.RQ_MOCREFNO) LEFT JOIN v_BlanketSODeliveryQtyByLine ON (MOC.MOC_OHNUMBER = v_BlanketSODeliveryQtyByLine.RQ_MOCNUMBER) AND (MOC.MOC_REF_NO = v_BlanketSODeliveryQtyByLine.RQ_MOCREFNO) LEFT JOIN IM ON MOC.MOC_PART_NO = IM.IM_KEY LEFT JOIN ONCORE_SM.dbo.NV_CUST_INFO CI ON IM.IM_CATALOG = CI.CUCODE WHERE ((MOC.MOC_USER_11 <> 0) AND (MOC.MOC_STATUS <> 4)) AND (@ProgMgr IS NULL OR CI.Team = @ProgMgr) AND (@CU_KEY IS NULL OR OH.OH_CUKEY = @CU_KEY) AND (@IM_KEY IS NULL OR IM.IM_KEY = @IM_KEY)) Select * from cte where (@Outstanding IS NULL OR (OpenQty <> 0)) order by TEAM, OH_CUKEY, MOC_PART_NO, OH_NUMBER, C_REF_NO, RQ_DATE_REQ, RQ_DATE_PROM>I'm getting the error on the last line in the WHERE clasue:
>USE PKDEMO5 >GO >SET ANSI_NULLS ON >GO >SET QUOTED_IDENTIFIER ON >GO >EXEC sp_DropProc 'sp_GetBlanketSalesOrders' >GO > >CREATE PROCEDURE sp_GetBlanketSalesOrders > @ProgMgr VARCHAR(25) = NULL, -- Program Manager > @CU_KEY VARCHAR(12) = NULL, -- Customer > @IM_KEY VARCHAR(30) = NULL, -- Item key > @Outstanding INT = NULL -- If 1, only include records where OpenQty <> 0 > >AS > > SELECT CI.TEAM AS ProgramMgr, > OH.OH_CUKEY AS Customer, > MOC.MOC_PART_NO AS Item, > IM.IM_REV AS Rev, > OH.OH_CUST_PO_NUM AS CustPONum, > OH.OH_NUMBER AS SPNum, > MOC.MOC_REF_NO AS SOLine, > MOC.MOC_USER_11 AS OrigBlanketQty, > ISNULL(v_BlanketSODeliveryQtyByLine.BookedQty, 0)AS QtyOrdered, > MOC_USER_11 - ISNULL(v_BlanketSODeliveryQtyByLine.BookedQty, 0) AS RemainingBlanketQty, > CASE WHEN MOC_USER_21 = '1901-01-01' THEN NULL ELSE MOC_USER_21 END AS BlanketExpDate, > MOC.MOC_PRICE AS LinePrice, > RQ.RQ_DATE_REQ AS RequiredDate, > RQ.RQ_DATE_PROM AS PromiseDate, > RQ.RQ_QTY_REQ AS RequiredQty, > RQ.RQ_QTY_ISS AS ShippedQty, > CASE WHEN RQ_STATUS = 'C' OR RQ_QTY_ISS > RQ_QTY_REQ THEN 0 ELSE RQ_QTY_REQ - RQ_QTY_ISS END AS OpenQty > FROM OH > INNER JOIN MOC ON OH.OH_NUMBER = MOC.MOC_OHNUMBER > LEFT JOIN RQ ON (MOC.MOC_OHNUMBER = RQ.RQ_MOCNUMBER) AND > (MOC.MOC_REF_NO = RQ.RQ_MOCREFNO) > LEFT JOIN v_BlanketSODeliveryQtyByLine ON (MOC.MOC_OHNUMBER = v_BlanketSODeliveryQtyByLine.RQ_MOCNUMBER) AND > (MOC.MOC_REF_NO = v_BlanketSODeliveryQtyByLine.RQ_MOCREFNO) > LEFT JOIN IM ON MOC.MOC_PART_NO = IM.IM_KEY > LEFT JOIN ONCORE_SM.dbo.NV_CUST_INFO CI ON IM.IM_CATALOG = CI.CUCODE > WHERE ((MOC.MOC_USER_11 <> 0) AND (MOC.MOC_STATUS <> 4)) AND > > (@ProgMgr IS NULL OR CI.Team = @ProgMgr) AND > (@CU_KEY IS NULL OR OH.OH_CUKEY = @CU_KEY) AND > (@IM_KEY IS NULL OR IM.IM_KEY = @IM_KEY) AND > (@Outstanding IS NULL OR (OpenQty <> 0)) > ORDER BY CI.TEAM, OH.OH_CUKEY, MOC.MOC_PART_NO, OH.OH_NUMBER, MOC.MOC_REF_NO, RQ.RQ_DATE_REQ, RQ.RQ_DATE_PROM >>