CASE statement in HAVING clause (1 Viewer)

[stephen81]

I'm writing a sql query in an adp where a few of the columns are calculated using CASE statements.
e.g.
SELECT columnA, columnB, CASE WHEN columnC IS NULL THEN 1 ELSE 0 END BooleanColumnC, columnD
GROUP BY ......
HAVING columnA = @parameter1, BooleanColumnC = @parameter2

When I try this though I am told BooleanColumnC is an invalid column name. I've had a look around and suspect this is because I'm trying to use an alias in a HAVING clause so...
I tried putting the whole CASE statement in the HAVING clause
i.e. HAVING columnA = @parameter1, CASE WHEN columnC IS NULL THEN 1 ELSE 0 END BooleanColumnC = @parameter2 but then I get the error message "ADO Error: Incorrect Syntax near 'BooleanColumnC' "
Any ideas?

 

[Kodo]

why don't you set a default value of 0 for ColumnC so when any new record is created ColumnC will have 0. Now ColumnC will never be NULL and you don't have to bother with the case statement.

 

[stephen81]

why don't you set a default value of 0 for ColumnC so when any new record is created ColumnC will have 0. Now ColumnC will never be NULL and you don't have to bother with the case statement.

Thanks Kodo but I see I perhaps wasn't very clear in my question.
The CASE statement I put in was just an example. It's actually dates I'm working with so my case statement is actually more like

CASE WHEN columnC<= GETDATE() THEN 1 ELSE 0 END booleanC

 

[stephen81]

OK, could be making some progress here. I've tried my CASE statements with out the alias after the END and so far they seem to be working.

 

[stephen81]

OK, could be making some progress here. I've tried my CASE statements with out the alias after the END and so far they seem to be working.

Yep. Definitely looks as though that did the trick.

 

[mgpa]

If it helps, you have missed out " AS " before "booleanC"