invalid column name in query

Question

invalid column name in query

Jonathan Brotto 1,076

I am writing a query in my ERP and getting an error and not sure how to go about it.

DECLARE @YEAR AS INT = 2022  
  
SELECT c.CardCode  
      ,c.CardName  
      ,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 3), 0) -   
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 3), 0) AS [Sales given year -3]  
      ,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 2), 0) -   
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 2), 0) AS [Sales given year -2]  
      ,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 1), 0) -   
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 1), 0) AS [Sales given year -1]  
      ,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR), 0) -   
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR), 0) AS [Sales given year]  
FROM OCRD c  
     INNER JOIN OSLP sp ON c.SlpCode = sp.SlpCode  
WHERE sp.SlpName like '%'  
AND [Sales given year -3] <= 2000 AND [Sales given year -2] <= 2000 AND [Sales given year -1] <= 2000 AND [Sales given year] <= 2000   
AND (([Sales given year -3] + [Sales given year -2] + [Sales given year -1] + [Sales given year]) != 0 )

Michael Taylor 61,116 Reputation points

2022-04-29T14:44:42.837+00:00

I believe you have already gotten the answer to this question over here. As stated there you need to wrap the column names in brackets if they have non-standard characters in them like spaces.

Please do not post variants of the same question you have already posted. Use the original thread you created. If you do need to create an additional question then please ensure you link back to the original question so others have context.

Answer accepted by question author

0 additional answers

Your answer

Michael Taylor 61,116 Reputation points

2022-04-29T14:44:42.837+00:00

I believe you have already gotten the answer to this question over here. As stated there you need to wrap the column names in brackets if they have non-standard characters in them like spaces.

Please do not post variants of the same question you have already posted. Use the original thread you created. If you do need to create an additional question then please ensure you link back to the original question so others have context.

Answer 1

If you don't want to use CTE:

Select * from (
SELECT sp.SlpName, c.CardCode
,c.CardName
,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 3), 0) -
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 3), 0) AS [Sales given year -3]
,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 2), 0) -
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 2), 0) AS [Sales given year -2]
,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 1), 0) -
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR - 1), 0) AS [Sales given year -1]
,ISNULL((select sum(h1.linetotal) from OINV h INNER JOIN INV1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR), 0) -
ISNULL((select sum(h1.linetotal) from ORIN h INNER JOIN RIN1 h1 ON h.docentry = h1.docentry where h.CardCode = c.CardCode and YEAR(h.DocDate) = @YEAR), 0) AS [Sales given year]
FROM OCRD c
INNER JOIN OSLP sp ON c.SlpCode = sp.SlpCode

       ) t

  WHERE  SlpName like '%'
  AND [Sales given year -3] <= 2000 AND [Sales given year -2] <= 2000 AND [Sales given year -1] <= 2000 AND [Sales given year] <= 2000 
  AND (([Sales given year -3] + [Sales given year -2] + [Sales given year -1] + [Sales given year]) <> 0 )

Share via

invalid column name in query

0 additional answers

Your answer