CODEWITHSUNDEEP
sqlsql-servert-sqlcase
Damon
Damon

Reputation: 11

SQL Case statement /where clause issues

I am trying to dynamically modify my where clause by using a declared variable in a case statement above. Forgive me if there is a simple answer to this that I'm missing, but I'm entirely self taught and still have a lot to learn. Here's a sample of the syntax. I am not going to bother with the entire syntax of the statement, just enough that hopefully you will understand what I'm trying to do.

declare @myvar1 double
declare @myvar2 double

SELECT
q1.host_po_nbr,
q1.etd,
CASE
    WHEN WEEKDAY(TODAY) = 1 Then 11
    WHEN WEEKDAY(TODAY) = 2 Then 12
    WHEN WEEKDAY(TODAY) = 3 Then 13
    WHEN WEEKDAY(TODAY) = 4 Then 14
    WHEN WEEKDAY(TODAY) = 5 Then 15
END @myvar1,
CASE
    WHEN WEEKDAY(TODAY) = 1 Then 5
    WHEN WEEKDAY(TODAY) = 2 Then 6
    WHEN WEEKDAY(TODAY) = 3 Then 7
    WHEN WEEKDAY(TODAY) = 4 Then 8
    WHEN WEEKDAY(TODAY) = 5 Then 9
END @myvar2,
FROM q1
WHERE q1.etd BETWEEN today - @myvar1 AND today - @myvar2

Upvotes: 1

Views: 154

Answers (2)

Sasha Pachev
Sasha Pachev

Reputation: 5326

Try this:

SELECT

q1.host_po_nbr,
q1.etd,
CASE
WHEN WEEKDAY(TODAY) = 1 Then 11
WHEN WEEKDAY(TODAY) = 1 Then 12
WHEN WEEKDAY(TODAY) = 1 Then 13
WHEN WEEKDAY(TODAY) = 1 Then 14
WHEN WEEKDAY(TODAY) = 1 Then 15
END myvar1,
CASE
WHEN WEEKDAY(TODAY) = 1 Then 5
WHEN WEEKDAY(TODAY) = 1 Then 6
WHEN WEEKDAY(TODAY) = 1 Then 7
WHEN WEEKDAY(TODAY) = 1 Then 8
WHEN WEEKDAY(TODAY) = 1 Then 9
END myvar2,

FROM q1
HAVING q1.etd BETWEEN today - myvar1 AND today - myvar2

This, however, could be inefficient. If this does not solve your problem, post more details, in particular, your table structure (SHOW CREATE TABLE) and a good description of what you are trying to achieve.

Upvotes: 0

D Stanley
D Stanley

Reputation: 152521

I'm assuming that you're only using variables to try and reference the calculation in the SELECT clause from your WHERE clause. You can't reference aliases from the SELECT in your WHERE clause (at least not in most SQL systems). The reason is that The WHERE clause is evaluated before the select, so those aliases do not exist at the time the WHERE clause is being evaluated. You can use a subquery, however:

SELECT * FFROM
   (SELECT
    q1.host_po_nbr,
    q1.etd,
    CASE
    WHEN WEEKDAY(TODAY) = 1 Then 11
    WHEN WEEKDAY(TODAY) = 1 Then 12
    WHEN WEEKDAY(TODAY) = 1 Then 13
    WHEN WEEKDAY(TODAY) = 1 Then 14
    WHEN WEEKDAY(TODAY) = 1 Then 15
    END myvar1,
    CASE
    WHEN WEEKDAY(TODAY) = 1 Then 5
    WHEN WEEKDAY(TODAY) = 1 Then 6
    WHEN WEEKDAY(TODAY) = 1 Then 7
    WHEN WEEKDAY(TODAY) = 1 Then 8
    WHEN WEEKDAY(TODAY) = 1 Then 9
    END myvar2,
    FROM q1
    ) q
    WHERE etd BETWEEN today - myvar1 AND today - myvar2

Upvotes: 2

Related Questions