# SQL column type converstion error?

1,071 Reputation points
2022-04-29T13:56:55.54+00:00

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
``````
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,736 questions

1. 17,631 Reputation points
2022-04-29T14:21:11.977+00:00

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
``````

2. 7,076 Reputation points
2022-04-29T14:33:37.587+00:00

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.

3. 5,311 Reputation points
2022-04-29T14:40:52.267+00:00

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

4. 77,576 Reputation points MVP
2022-04-29T21:29:37.477+00:00

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. :-)

5. 3,396 Reputation points
2022-05-02T01:28:30.73+00:00

Welcome to Microsoft T-SQL Q&A Forum!

``````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