Solved

case in Where clause

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
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.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

17 Experts available now in Live!

Get 1:1 Help Now