?
Solved

Using Like within a case statement

Posted on 2007-10-02
5
Medium Priority
?
788 Views
Last Modified: 2008-01-09
I need to have part of a question not have Like and the other to have like - can't seem to get both to reside in the same statement...
In SQL 2000
It appears that the WHEN word needs to be AFTERthe field unless I use the LIKE then the WHEN  needs to be BEFORE the field ... as in:
Case payrolcd WHEN  'ermedp' then uprtrxam
vs.
Case WHEN payrolcd like 'erm%' then uprtrxam
I am trying to encompass both in the same statement w/ a sum - something like:
SUM(CASE pyrlrtyp WHEN 2 THEN
           case payrolcd when 'ermedp'  then uprtrxam
                                  when like 'fer%'  then uprtrxam
 ELSE 0 END ELSE 0 END)   ,


Any way to do this ...
0
Comment
Question by:Tunkster2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 19998813
SUM(CASE WHEN pyrlrtyp  = 2 THEN
           case when payrolcd  = 'ermedp'  then uprtrxam
                                  when payrolcd like 'fer%'  then uprtrxam
 ELSE 0 END ELSE 0 END)   ,
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19998831
SUM((CASE WHEN pyrlrtyp = 2 THEN (CASE WHEN payrolcd LIKE 'erm%' OR payrolcd LIKE 'fer%' THEN uprtrxam ELSE 0 END) ELSE 0 END))
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 19999047
SUM(
CASE WHEN pyrlrtyp = 2 THEN
     CASE payrolcd
        WHEN LIKE 'erm%' THEN uprtxam
        WHEN LIKE 'fer%'   THEN uprtxam
     END
ELSE 0 END)
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 19999052
WHEN LIKE (valley girl)
0
 

Author Comment

by:Tunkster2
ID: 19999702
Appreciate it - I didn't realize that in switching the WHEN to the back from the front I needed to add the " =  " sign... duh - your answer did not highlight that - so it took me a bit to apply correctly...
Thanks for the quick response !!!
I'll have another in a minute
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

839 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