Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

t-sql query help on case in where clause

Posted on 2011-03-10
13
Medium Priority
?
552 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:jfreeman2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35099797

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
0
 

Author Comment

by:jfreeman2010
ID: 35099914
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35100263
could you please clarify "does not work" ...
any error messages?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 52

Expert Comment

by:_agx_
ID: 35100455
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.

0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 35100651
Is there a reason you need a QoQ at all? They're not always as performant as you'd think and they're very rudimentary.  They don't support much beyond basic SELECT's.  IMO, you'd be better off doing the whole thing in a regular database query.  Then you'd have access to all of SQL Server 2008's features.

If you really *must* use a QoQ, use the CASE in your database query to add a calculated column called "AgeRangeValue".  Then use it in your QoQ:
     
       WHERE   test_tb1.age_range = test_tb2.AgeRangeValue
0
 

Author Comment

by:jfreeman2010
ID: 35100743
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 (


0
 

Author Comment

by:jfreeman2010
ID: 35100772
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.

0
 

Author Comment

by:jfreeman2010
ID: 35100794
My first case this the about is working, but the second part of the case with count have error.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35100908
Well like the error message says, you can't use aggregates like COUNT(...) in that spot. Can you post the full sql?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35102060
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?
0
 

Author Comment

by:jfreeman2010
ID: 35109391
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...
0
 

Author Closing Comment

by:jfreeman2010
ID: 35109717
THANK YOU.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35110647
@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 :)
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question