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
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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
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.
A subquery to access these aliases. (CTE is a clean way or you can repeat your alias content ).
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. :-)
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.