?
Solved

case in Where clause

Posted on 2013-02-04
5
Medium Priority
?
331 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
[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
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 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 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