jfreeman2010
asked on
t-sql query help on case in where clause
I have a t-sql query and can't make it work with the case in where:
select test_tb1.test_id,
test_tb1.test_code,
test_tb1. age_range,
test_tb2.age
from test_tb1, test_tb2
where test_tb1.id = test_tb2.id
and test_tb1.test_code = 'o'
and test_tb1.age_range =
case
when test_tb2.age < 21 then '10-20'
when test_tb2.age >=21 and test_tb2.age < 51 then '20-50'
when test_tb2.age > 50 then '50+'
end
select test_tb1.test_id,
test_tb1.test_code,
test_tb1. age_range,
test_tb2.age
from test_tb1, test_tb2
where test_tb1.id = test_tb2.id
and test_tb1.test_code = 'o'
and test_tb1.age_range =
case
when test_tb2.age < 21 then '10-20'
when test_tb2.age >=21 and test_tb2.age < 51 then '20-50'
when test_tb2.age > 50 then '50+'
end
ASKER
Hi ewangoya,
thank you very much for the response. It still not working after the medication...
I am using this query in coldfusion and also is a query of query ( both test_tb1 and test_tb2 are queries).
I am not sure may be this is a coldfusion and query of query problem.
thank you very much for the response. It still not working after the medication...
I am using this query in coldfusion and also is a query of query ( both test_tb1 and test_tb2 are queries).
I am not sure may be this is a coldfusion and query of query problem.
could you please clarify "does not work" ...
any error messages?
any error messages?
I am using this query in coldfusion and also is a query of query ( both test_tb1 and test_tb2 are queries).
If you mean you're trying to use that SQL in a QoQ .. you can't. When you run a QoQ's you're running an in memory query. Not a query against your db. So you can only use the operators QoQ's support. They do not support CASE.
If you mean you're trying to use that SQL in a QoQ .. you can't. When you run a QoQ's you're running an in memory query. Not a query against your db. So you can only use the operators QoQ's support. They do not support CASE.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, if coldfusion not supporting case in QOQ. then I need to move the logic up to one level.... I am trying that now and have problem, here is the query. THIS IS NOT A QoQ:
select test_tb1.test_id, test_tb1.test_code, test_tb1. age_range, test_tb1.initial
from test_tb1, test_tb2, test_tb3
where test_tb1.id = test_tb2.id
and test_tb1.p_id = test_tb3.p_id
and test_tb1.test_code = 'b'
or
( test_tb1.test_code = 'o'
and test_tb1.age_range =
case
when test_tb2.age < 21 then
'10-20'
when test_tb2.age >=21 and test_tb2.age < 51 then
'20-50'
else
'50+'
end
and
test_tb1.initial =
case when (
select test_tb1.test_id, test_tb1.test_code, test_tb1. age_range, test_tb1.initial
from test_tb1, test_tb2, test_tb3
where test_tb1.id = test_tb2.id
and test_tb1.p_id = test_tb3.p_id
and test_tb1.test_code = 'b'
or
( test_tb1.test_code = 'o'
and test_tb1.age_range =
case
when test_tb2.age < 21 then
'10-20'
when test_tb2.age >=21 and test_tb2.age < 51 then
'20-50'
else
'50+'
end
and
test_tb1.initial =
case when (
ASKER
Sorry, the last part of the case should be:
case when ( select count (pp_id)
from test_tb t
inner join test_tb5 t6
on t.id = t6.id ) > 1 then 'n'
else 'y'
end
)
)
and I am getting error :
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
case when ( select count (pp_id)
from test_tb t
inner join test_tb5 t6
on t.id = t6.id ) > 1 then 'n'
else 'y'
end
)
)
and I am getting error :
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
ASKER
My first case this the about is working, but the second part of the case with count have error.
Well like the error message says, you can't use aggregates like COUNT(...) in that spot. Can you post the full sql?
select count (pp_id) from test_tb t inner join test_tb5 t6
Also what's the relationship between those 2 tables and the tables in the query?
Also what's the relationship between those 2 tables and the tables in the query?
ASKER
Hi agx,
Thank you very much for helping, I full query are to much to post here, its a 8 tables join, I will try to work it out, and if still has problem, will come up a example in a hour to see if you can help...
Thank you very much for helping, I full query are to much to post here, its a 8 tables join, I will try to work it out, and if still has problem, will come up a example in a hour to see if you can help...
ASKER
THANK YOU.
@jfreeman2010 - Did you get it working? I tried to re-write an example, but couldn't figure out how to relate the 2 tables in the subquery to the rest of the tables :)
use else for the last case option
select test_tb1.test_id, test_tb1.test_code, test_tb1. age_range, test_tb2.age
from test_tb1, test_tb2
where test_tb1.id = test_tb2.id
and test_tb1.test_code = 'o'
and test_tb1.age_range =
case
when test_tb2.age < 21 then
'10-20'
when test_tb2.age >=21 and test_tb2.age < 51 then
'20-50'
else
'50+'
end