cancel
Showing results for 
Search instead for 
Did you mean: 

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

0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Johan_H
Active Contributor

Hi Nic,

Please change this:

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

to this:

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

Regards,

Johan

Thanks Johan!!

Works a treat!

Answers (0)