SQL column type converstion error?

Question

In my ERP there is a converting column type error I think. Do I use a cast to fix this?

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 year3]  
      ,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 year2]  
      ,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 year1]  
      ,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 year3' <= 2000 AND 'Sales given year2' <= 2000 AND 'Sales given year1' <= 2000 AND 'Sales given year' <= 2000   

Answer 1

These are field names. You need to use [ ] around them instead of ''

AND 'Sales given year3' <= 2000 AND 'Sales given year2' <= 2000 AND 'Sales given year1' <= 2000 AND 'Sales given year' <= 2000 

Answer 2

You cannot use newly created column names right away, you need to use your original query as CTE and then add your conditions right after that as a separate query.

Answer 3

A subquery to access these aliases. (CTE is a clean way or you can repeat your alias content ).

Answer 4

What people are telling you to write is this:

   DECLARE @YEAR AS INT = 2022  
     
   ; WITH BaseQuery AS (          
        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 year3]  
              ,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 year2]  
              ,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 year1]  
              ,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 '%'  
   )  
   SELECT CardCode, CardName,"Sales given year3", "Sales given year2", "Sales given year1", "Sales given year"  
   FROM   BaseQuery  
   WHERE  "Sales given year3" <= 2000   
     AND  "Sales given year2" <= 2000   
     AND  "Sales given year1" <= 2000   
     AND  "Sales given year" <= 2000  

The reason you need to do it this is because logically, an SQL query is evaluated in the order FROM/JOIN - WHERE - GROUP BY - HAVING - SELECT - ORDER BY. Thus, values you define in the SELECT list are not available when the WHERE clause is evaluated.

As for [] vs "", they are equivalent. "" is ANSI standard, while [] are Microsoft proprietary. And, more importantly, the double quotes are easier to type on my Swedish keyboard. :-)

Answer 5

Hi，@Jonathan Brotto

Welcome to Microsoft T-SQL Q&A Forum!

Please check this：

DECLARE @YEAR AS INT = 2022  
    select CardCode, CardName,[Sales given year3],[Sales given year2], [Sales given year1], [Sales given year]  
    from  
    (  
       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 year3]  
               ,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 year2]  
               ,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 year1]  
               ,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 '%'  
    )Test     
     WHERE [Sales given year3] <= 2000   
      AND  [Sales given year2] <= 2000   
      AND  [Sales given year1] <= 2000   
      AND  [Sales given year] <= 2000  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.