Skip to Content
0
Oct 13 at 02:22 AM

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'xxxxx'

95 Views

Hi,

When I execute the following I get an error:

/* select * from OITB x */

DECLARE @WHO AS NVARCHAR(20)

SET @WHO = /* x.ItmsGrpNam */ [%0]

SELECT F1.CardCode,F1.[PART NUM],F1.Dscription, F1.[ItmsGrpCod], F1.[name], case when F1.U_FixDate <> '' then F1.U_FixDate when F1.ShipDate = F1.DocDueDate then F1.ShipDate else F1.DocDueDate end as 'Order Out Date', F1.DocNum, F1.[qty] AS 'Order Qty', F1.[Open Qty], F1.[On Hand], F1.Minlevel, F1.MaxLevel, F1.[PO Balance], F1.[SO Balance], F1.[On Hand] + sum(F1.[PO Balance]) OVER (PARTITION BY F1.[PART NUM] ORDER BY F1.DocDueDate) - sum(F1.[SO Balance]) OVER (PARTITION BY F1.[PART NUM] ORDER BY F1.DocDueDate) AS 'Running Balance', case when F1.[PO Balance] > 0 then F1.[PO Balance] when F1.[On Hand] + sum(F1.[PO Balance]) OVER (PARTITION BY F1.[PART NUM] ORDER BY F1.DocDueDate) - sum(F1.[SO Balance]) OVER (PARTITION BY F1.[PART NUM] ORDER BY F1.DocDueDate) > F1.Minlevel then '0' when (F1.[On Hand] -F1.[qty])< F1.Minlevel then F1.[On Hand] + sum(F1.[PO Balance]) OVER (PARTITION BY F1.[PART NUM] ORDER BY F1.DocDueDate) - sum(F1.[SO Balance]) OVER (PARTITION BY F1.[PART NUM] ORDER BY F1.DocDueDate) + (F1.Minlevel-F1.[On Hand]) else '0' end as 'Ámt To Make'

FROM

(SELECT T0.CardCode,T5.ItemCode AS 'PART NUM',T5.Dscription, T0.DocDueDate, T5.ShipDate, T5.U_FixDate, T0.DocNum, T5.Quantity AS 'qty', T5.OpenCreQty AS 'Open Qty', T6.OnHand AS 'On Hand', T6.IsCommited AS 'Commited', T6.ItmsGrpCod, T7.ItmsGrpNam AS 'name', T6.Minlevel, T6.MaxLevel, '0' AS 'PO Balance', T5.OpenCreQty AS 'SO Balance' FROM ORDR T0 LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode LEFT JOIN OCPR T2 ON T0.CntctCode = T2.CntctCode LEFT JOIN (SELECT DocEntry, SUM(GrssProfit) AS GrossProfit FROM RDR1 GROUP BY DocEntry) T3 ON T0.DocEntry = T3.DocEntry LEFT JOIN RDR1 T5 ON T0.DocEntry = T5.DocEntry LEFT JOIN OITM T6 ON T5.ItemCode = T6.ItemCode LEFT JOIN OITB T7 ON T6.ItmsGrpCod = T7.ItmsGrpCod UNION ALL SELECT T0.CardCode, T5.ItemCode AS 'PART NUM',T5.Dscription,T0.DocDueDate, T5.ShipDate, T5.U_FixDate, T0.DocNum, T5.Quantity AS 'qty', T5.OpenCreQty AS 'Open Qty', T6.OnHand AS 'On Hand', T6.IsCommited AS 'Commited', T6.ItmsGrpCod, T7.ItmsGrpNam AS 'name', T6.Minlevel, T6.MaxLevel, T5.OpenCreQty AS 'PO Balance', '0' AS 'SO Balance' FROM OPOR T0 LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode LEFT JOIN OCPR T2 ON T0.CntctCode = T2.CntctCode LEFT JOIN (SELECT DocEntry, SUM(GrssProfit) AS GrossProfit FROM RDR1 GROUP BY DocEntry) T3 ON T0.DocEntry = T3.DocEntry LEFT JOIN POR1 T5 ON T0.DocEntry = T5.DocEntry LEFT JOIN OITM T6 ON T5.ItemCode = T6.ItemCode LEFT JOIN OITB T7 ON T6.ItmsGrpCod = T7.ItmsGrpCod ) F1

WHERE F1.[name] = @WHO AND F1.DocDueDate >= GETDATE()

ORDER BY F1.[PART NUM] DESC, [Order Out Date] ASC


I get the result when I remove the query (i.e. remove the first 3 lines and replace "WHERE F1.[name] = @WHO AND F1.DocDueDate >= GETDATE()" with "WHERE F1.[name] = 'name' AND F1.DocDueDate >= GETDATE()").

I really want the user to select the input for the name. Any suggestions on resolving the issue?

Thanks