Solved

case in Where clause

Posted on 2013-02-04
5
322 Views
Last Modified: 2013-02-05
I am trying to use a case in a where clause, but nothing is being retrieved. There are some that meet the criteria. This is my where clause:

WHERE (CASE
      WHEN Substring(ESGACTTR.ACCT_NO,5,6) = '12-011' and @DeptGroup = 'D1' THEN 1
        WHEN Substring(ESGACTTR.ACCT_NO,5,6) = '12-012' and @DeptGroup = 'D1' THEN 1
        WHEN Substring(ESGACTTR.ACCT_NO,5,6) = '12-210' and @DeptGroup = 'D1' THEN 1
        WHEN Substring(ESGACTTR.ACCT_NO,5,6) = '12-211' and @DeptGroup = 'D1' THEN 1
        WHEN Substring(ESGACTTR.ACCT_NO,5,6) = '12-217' and @DeptGroup = 'D1' THEN 1
        WHEN Substring(ESGACTTR.ACCT_NO,5,6) = '12-218' and @DeptGroup = 'D1' THEN 1
        WHEN Substring(ESGACTTR.ACCT_NO,5,6) = '12-134' and @DeptGroup = 'D1' THEN 1
        WHEN Substring(ESGACTTR.ACCT_NO,5,6) = '12-212' and @DeptGroup = 'D2' THEN 1
        WHEN Substring(ESGACTTR.ACCT_NO,5,6) = '12-213' and @DeptGroup = 'D3' THEN 1
        WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '13' and @DeptGroup = 'D13' THEN 1
        WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '14' and @DeptGroup = 'D13' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '15' and @DeptGroup = 'D15' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '16' and @DeptGroup = 'D16' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '17' and @DeptGroup = 'D17' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,6) = '12-220' and @DeptGroup = 'D16' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '18' and @DeptGroup = 'D18' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '19' and @DeptGroup = 'D19' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '20' and @DeptGroup = 'D20' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '21' and @DeptGroup = 'D21' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '23' and @DeptGroup = 'D23' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '35' and @DeptGroup = 'D35' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '86' and @DeptGroup = 'D86' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '50' and @DeptGroup = 'D50' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '60' and @DeptGroup = 'D50' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '99' and @DeptGroup = 'D99' THEN 1
    ELSE 0
  END
  ) = 1

What am I doing wrong?
0
Comment
Question by:qbjgqbjg
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38853100
Seems like your "case' statement is retutning 0. chekc the substring value and make sure that its correct
0
 
LVL 39

Expert Comment

by:appari
ID: 38853260
can you post test data?
also for both '50' and '60' you are checking @DeptGroup = 'D50' check if that is correct.

    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '50' and @DeptGroup = 'D50' THEN 1
    WHEN Substring(ESGACTTR.ACCT_NO,5,2) = '60' and @DeptGroup = 'D50' THEN 1
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38853368
your statement appears ok ... in that it should function albeit probably with less than optimum performance ....

why not just write it as

where (substring(xxxm,1,2)='xx' and yyy='xx' )
or (substring(xxxm,1,2)='xxy' and yyy='xx')
or ...

and avoid the additonal intermediate result checking overheads....
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38853994
I think I would put all that logic into a table, and join to that table. .. like this
create table tbl_my_conditions
  ( DeptGroup  varchar(100)
 , ACCT_NO_START int
 , ACCT_NO_LEN int
 , ACCT_NO_VALUE varchar(100)
)

Open in new window

and fill it like this:
insert into tbl_my_conditions ( deptgroup, acct_no_start, acc_no_len, acct_no_value)
      select 'D1', 5,6, '12-011' 
union all select 'D1', 5,6, '12-012'
 ... etc ... 

Open in new window


and your code would then go like this:
select *
  from ESGACTTR t
  join tbl_my_conditions c
   on c.DeptGroup = @DeptGroup
  and Substring(t.ACCT_NO, c.ACCT_NO_START, c.ACCT_NO_LEN) = c.ACC_NO_VALUE 

Open in new window


adding more values/conditions to match will be easy, no code to be modified.

now, in regards to your problem, if you don't want to go "my way", you put the expression you have to the SELECT, comment it out in the WHERE, and see in which rows it returns 1 or 0 ...
0
 

Author Closing Comment

by:qbjgqbjg
ID: 38855282
Thanks very much.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now