February 1, 2006 at 11:48 am
I'm having trouble getting a case statement to resolve the way I expect.
I'm hoping someone can point me in the right direction! Thanks in advance.
Here's a simplified example of what I am trying to accomplish:
create table test (id int,data varchar(10))
insert into test (id,data) values (50,'1,2')
select case when cast(1 as varchar) in
(select data from test where id = 50)
then 'THEN'
else 'ELSE' --result
end
--But it SHOULD result in THEN right?? Why doesn't it?
--This is what I think it should resolve to and it does result with THEN!
select case when 1 in
(1,2)
then 'THEN' --result
else 'ELSE'
end
That's a simplified example of a view I'm trying to create. The view should exclude customer's attribute of snowbird during the month of 10,11,12,1,2,3. Snowbird is an attribute_id and the months are stored in a configuration table.
ALTER VIEW dbo.vwDontMail
AS
SELECT customers.pkid as customer_id,
custattrib_id
FROM customers
INNER JOIN customer_attributes ca ON customers.pkid = ca.customer_id
WHERE attribute_id in (71,75,76) --3077
and 134 =
case when cast(datepart(mm,getdate()) as varchar)
in (cast((select cifsetting_data from cifsettings where cifsetting_id = 260) as varchar))
then attribute_id --should use this on month 10,11,12,1,2,3
else 134 --should use this otherwise, basically does nothing
end
February 1, 2006 at 12:35 pm
You would need Dynamic SQL to achieve what you're trying to do. The comma-separated list in an IN ('val1, val2') construct cannot be the result of another query at runtime.
You'd be better off with a set-based approach, and a table design without the comma separated "array":
create table test (id int,data varchar(10))
-- 2 rows, make it a set
insert into test (id,data) values (50,1)
insert into test (id,data) values (50,2)
select case when cast(1 as varchar) in
(select data from test where id = 50)
then 'THEN'
else 'ELSE' --result
end
February 1, 2006 at 1:53 pm
Thanks for clarifying why it didn't work. I didn't know the in clause couldn't be the result of a query at runtime.
If I HAD to make it work with the existing table structure I could do this.
create table test (id int,data varchar(10))
insert into test (id,data) values (50,',1,2,3,')
select case when (select data from test where id = 50)
like '%,'+cast(datepart(mm,getdate()) as varchar)+',%'
then 'THEN'
else 'ELSE'
end
Thanks. Kim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply