February 28, 2003 at 5:36 am
Hello guru's out there.
I have a question about case statements.
Below is an example from the books online
SELECT CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END
as 'Example',
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
and Example = 'Business'
In the real life situation the case is very complicated and compares data from several tables. But i want to know if the result of the case is NULL....
So i want to know if the column 'Example'is null....
I cannot make it work does anybody have any ideas ?
The trick that is used now is to insert everything in a temp table and
retrieve the record whre it is not null. This is very slow.
But the reason they did this is because they could not the value retrieved in the case in the where clause...
Anybody knows how to make this work.....
February 28, 2003 at 5:52 am
Put the column name inside the case statment, then you can do something like this:
SELECT CASE
WHEN type = 'popular_comp' THEN 'Popular Computing'
WHEN type = 'mod_cook' THEN 'Modern Cooking'
WHEN type = 'business' THEN 'Business'
WHEN type = 'psychology' THEN 'Psychology'
WHEN type = 'trad_cook' THEN 'Traditional Cooking'
WHEN type is null THEN ''
ELSE 'Not yet categorized'
END as Example,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
and Example = 'Business'
February 28, 2003 at 5:55 am
Thanks for the help, but I tried it already it give me :
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Example'.
February 28, 2003 at 5:57 am
Yopu will need to change the last line to:
and Type = 'Business'
February 28, 2003 at 6:09 am
Yes in this case that works.
But it was a simplified example.
Th real query doesn't take a value from a single column or table but uses
3 joined tables and several fields from every table to construct a special date.
select example = case
whenb a
use table 2, field 1 and 2
when b
use table 3, field 4 and 5
when c
even another case statement here.
end case
end case
I need to see if this resulting date is NULL...
February 28, 2003 at 6:17 am
SELECT CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END
as 'Example',
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
and Example = 'Business'
This is a poor example and for you to find Example = 'Business' you would need to use the real field name Type so Type = 'Business'
Also don't quote column names like this AS 'Shortened Title', instead do AS [Shortened Title]
However if you are going to do type = 'Business' there is no reason the the CASE at all as you will only return type = 'business' so instead it would be better to do
SELECT
Type as Example,
CAST(title AS varchar(25)) AS [Shortened Title],
price AS Price
FROM titles
WHERE price IS NOT NULL
and Type = 'Business'
You say this cam from BOL, which SQL Version and is the original or a variation you made?
February 28, 2003 at 6:26 am
Antares you are missing the point here,
it is indeed modified from the books online.
But the case doesn't use a single column, it uses 12 different columns spread over 3 tables.
So i cannot say type='bussiness'
I would not know wich field to choose
That is why I asked help from the forum
February 28, 2003 at 6:49 am
Ok, i think we're all up to speed now!
As you cannot use the column name Example in the where clause, another other option is to reproduce the case statement in the where clause:
SELECT CASE
WHEN type = 'popular_comp' THEN 'Popular Computing'
WHEN type = 'mod_cook' THEN 'Modern Cooking'
WHEN type = 'business' THEN 'Business'
WHEN type = 'psychology' THEN 'Psychology'
WHEN type = 'trad_cook' THEN 'Traditional Cooking'
WHEN type is null THEN ''
ELSE 'Not yet categorized'
END as Example,
CAST(title AS varchar(25)) AS [Shortened Title],
price AS Price
FROM titles
WHERE price IS NOT NULL
and CASE
WHEN type = 'popular_comp' THEN 'Popular Computing'
WHEN type = 'mod_cook' THEN 'Modern Cooking'
WHEN type = 'business' THEN 'Business'
WHEN type = 'psychology' THEN 'Psychology'
WHEN type = 'trad_cook' THEN 'Traditional Cooking'
WHEN type is null THEN ''
ELSE 'Not yet categorized'
END = 'business'
February 28, 2003 at 8:07 am
Actually instead of spounting off a query can you give me what your actual base query is and how the tables relate and what you want to accomplish. I just don't feel we have enough info to give you the best answer and whys.
March 2, 2003 at 6:35 am
I think I understand your question. One way to deal with it is nested queries. For example you want:
Select <case..... very long case....> as X
from table1, table2, table3, etc.
where <case..... very long case....> = "XYZ"
is very messy, may or may not even syntax right depending on complexity. Try this:
select *
from
(
Select <case..... very long case....> as X
from table1, table2, table3, etc.
) as q
where X = "XYZ"
It can clean up the syntax a lot. It may or may not have a performance impact however, depending on how the optimizer handles the condition outside the inner select in relation to the result set from inside.
Is that what you're looking for?
March 3, 2003 at 2:53 am
Thanks Fergusson.
I have tried numerous things and nothing seems to work.
What i do now is insert them in a mem table and then select the value I want.
Not a nice way but it was a very messy query.
It used a temp table first and i used a memory table insted.
query is now fast.
from 1500 ms down to 15 ms.
I would have liked a real select without the fuzzy logic but
i think it cannot be done.
Thanks a lot guys for the hints and tips.
March 3, 2003 at 6:58 am
If you want to eliminate using a temp table couldn't you do something like this:
Select
CASE IsNull(T.Column1,'Null')
WHEN 'Null' THEN 'Do Something'
ELSE
END 'Example'
FROM
(
SELECT T1.Column1,T2.Column2,T3.Column3
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.T1ID = T2.T1ID
INNER JOIN TABLE3 T3 ON T1.T1ID=T3.T1ID
WHERE T1.SomeColumn='true'
) AS T
WHERE T2.Column2 = 'SomeValue'
Does this help you out any?
March 4, 2003 at 7:36 pm
Try this one
Select *
From
( SELECT CASE
WHEN type = 'popular_comp' THEN 'Popular Computing'
WHEN type = 'mod_cook' THEN 'Modern Cooking'
WHEN type = 'business' THEN 'Business'
WHEN type = 'psychology' THEN 'Psychology'
WHEN type = 'trad_cook' THEN 'Traditional Cooking'
WHEN type is Null then Null
ELSE 'Not yet categorized'
END
as 'Example',
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM Pubs.dbo.titles
WHERE price IS NOT NULL ) As C
Where Example Is Null
Hendra
March 4, 2003 at 7:36 pm
Try this one
Select *
From
( SELECT CASE
WHEN type = 'popular_comp' THEN 'Popular Computing'
WHEN type = 'mod_cook' THEN 'Modern Cooking'
WHEN type = 'business' THEN 'Business'
WHEN type = 'psychology' THEN 'Psychology'
WHEN type = 'trad_cook' THEN 'Traditional Cooking'
WHEN type is Null then Null
ELSE 'Not yet categorized'
END
as 'Example',
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM Pubs.dbo.titles
WHERE price IS NOT NULL ) As C
Where Example Is Null
Hendra
March 5, 2003 at 3:08 am
Hendra YOU ARE A KING !
thanks A LOT !!!!!!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply