Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Invalid Column Name Error
Message
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
01435010
Message ID:
01435019
Views:
45
If you answer at the top, it's easier to read :)

>>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
>>
>>
>>OpenQty is defined as the last ite in the field list
>
>You can't use a field that is defined in the main query in WHERE clause.
>That field didn't exists yet.
>You should use the same expression in the WHERE or use the whole query as a derived table and then put WHERE.
If it's not broken, fix it until it is.


My Blog
Previous
Reply
Map
View

Click here to load this message in the networking platform