Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Invalid Column Name Error
Message
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Divers
Thread ID:
01435010
Message ID:
01435016
Vues:
47
You can not use fields created in the query in the WHERE clause. The only place where you can use new fields is ORDER BY clause.

You may either repeat the same condition in the where clause that creates this field or remove this condition from your original query, use your original as derived table or CTE and add an extra condition to it.

E.g.
; 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
>
>
>OpenQty is defined as the last ite in the field list
If it's not broken, fix it until it is.


My Blog
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform