invalid column name in query

Question

invalid column name in query

Jonathan Brotto 1,071

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 41,626 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.

Michael Taylor 41,626 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 )

invalid column name in query

0 additional answers

Activity