Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

case in Where clause

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

618 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