Solved

case in Where clause

Posted on 2013-02-04
5
326 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 143

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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 I will describe the Detach & Attach 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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

860 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