using CASE stmt in SELECT in HAVING clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using CASE stmt in SELECT in HAVING clause

Author

Topic

markusf
Starting Member

15 Posts

Posted - 2006-11-13 : 06:26:15

I have a fairly complex query where I in the SELECT stmt, have a nested CASE stmt.

This is an outline:

SELECT
(CASE ..
CASE ..
END
) AS col1
FROM ...
WHERE ...
HAVING col1 <> 'D'

The error I get is "Invalid column name 'listing_type'."

Is there any way I can use the alias name in the HAVING clause in SQLServer 2000 ?

--
Markus Foss, Norway

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-13 : 06:28:57

The error I get is "Invalid column name 'listing_type'."

Post your code so that we can guide you along.

Is there any way I can use the alias name in the HAVING clause in SQLServer 2000

Yes, may be by using derived tables, in the query.

Chirag

http://chirikworld.blogspot.com/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-13 : 06:29:30

nope. you have to put your case in the having.
or you can do
select * from
(your query without having) t1
where col1 <> 'D'

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

markusf
Starting Member

15 Posts

Posted - 2006-11-13 : 06:47:04

Spirit1: Thank you for your tip about using derived tables :-)

But is duplicating the CASE-stmt down to the HAVING clause more efficient than using a derived table (and moving the ORDER BY to the outer SELECT) ?

I am really amazed by the short time it takes to get good answers in this forum!!! :-D

--
Markus Foss, Norway

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-13 : 06:47:59

or use the expression there

Having Case when ........ END<>'D'

Also note that you should use group by when using HAVING

or post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-13 : 06:59:02

i'd use a derived table.
having is more complex than where.

also as madhivanan points out you need a group by. or are you really just want that case in the where part?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-13 : 07:00:47

quote:
Originally posted by markusf

Spirit1: Thank you for your tip about using derived tables :-)

But is duplicating the CASE-stmt down to the HAVING clause more efficient than using a derived table (and moving the ORDER BY to the outer SELECT) ?

I am really amazed by the short time it takes to get good answers in this forum!!! :-D

--
Markus Foss, Norway

Dont think much of performance draw back will be there.. if you compare the Query plan then you will get the better idea..

Chirag

http://chirikworld.blogspot.com/

markusf
Starting Member

15 Posts

Posted - 2006-11-13 : 07:07:25

Thanks for all the good input!

I'll use a derived table for my query :-)

--
Markus Foss, Norway

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-11-13 : 07:29:38

Ey, a fellow norwegian!! I think this is the first time I've ever seen another norwegian here...you are MOST WELCOME my friend! Umh, your'e not that rally-guy are you?? Guess he's a little bit too young to be fiddeling with sql-server

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

markusf
Starting Member

15 Posts

Posted - 2006-11-15 : 03:37:02

Lumbago: No, but I actually got called up by "Asker Budstikke" (A norwegian newspaper), asking for a comment on a rally the day before ;-)

--
Markus Foss, Norway

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources