Question Regarding SQL Parameter Query

Michele Smith

Hello: I need help with this:

SELECT *
FROM
(SELECT
CASE
WHEN InvoiceMonth IN (1,2,3) THEN 1
WHEN InvoiceMonth IN (4,5,6) THEN 2
WHEN InvoiceMonth IN (7,8,9) THEN 3
WHEN InvoiceMonth IN (10,11,12) THEN 4
END as Quarter
FROM viewSalesTracker_Model1
WHERE Quarter = @Quarter

The error I am receiving is: Error Source: .Net SqlClient Data Provider Error Message: Must declare the scalar variable "@Quarter".

ZurdoDev

It means that the compiler does not know what @Quarter is. You have not declared it anywhere.

Social Media - A platform that makes it easier for the crazies to find each other. Everyone is born right handed. Only the strongest overcome it. Fight for left-handed rights and hand equality.

Victor Nijegorodov

Michele Smith wrote:

Error Source: .Net SqlClient Data Provider Error Message: Must declare the scalar variable "@Quarter".

Then declare this "@Quarter"variable!

Richard Deeming

Once you've declared the @Quarter variable or parameter, you'll run into another error:

Invalid column name 'Quarter'.

You can't reference an alias introduced in the SELECT clause within the WHERE clause of the same statement. You either have to use a sub-query, a common table expression, or duplicate the logic of the alias in the WHERE clause.

SELECT Quarter
FROM
(
SELECT CASE
WHEN InvoiceMonth IN (1,2,3) THEN 1
WHEN InvoiceMonth IN (4,5,6) THEN 2
WHEN InvoiceMonth IN (7,8,9) THEN 3
WHEN InvoiceMonth IN (10,11,12) THEN 4
END As Quarter
FROM viewSalesTracker_Model1
)
WHERE Quarter = @Quarter;

Or:

WITH cteQuarters As
(
SELECT CASE
WHEN InvoiceMonth IN (1,2,3) THEN 1
WHEN InvoiceMonth IN (4,5,6) THEN 2
WHEN InvoiceMonth IN (7,8,9) THEN 3
WHEN InvoiceMonth IN (10,11,12) THEN 4
END As Quarter
FROM viewSalesTracker_Model1
)
SELECT Quarter
FROM cteQuarters
WHERE Quarter = @Quarter;

Or:

SELECT CASE
WHEN InvoiceMonth IN (1,2,3) THEN 1
WHEN InvoiceMonth IN (4,5,6) THEN 2
WHEN InvoiceMonth IN (7,8,9) THEN 3
WHEN InvoiceMonth IN (10,11,12) THEN 4
END As Quarter
FROM viewSalesTracker_Model1
WHERE CASE
WHEN InvoiceMonth IN (1,2,3) THEN 1
WHEN InvoiceMonth IN (4,5,6) THEN 2
WHEN InvoiceMonth IN (7,8,9) THEN 3
WHEN InvoiceMonth IN (10,11,12) THEN 4
END = @Quarter;

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer