Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL - CASE STATEMENT (EXPRESSION)

Posted on 2007-10-01
11
Medium Priority
?
397 Views
Last Modified: 2010-03-20
Hello all,
My question today is the correct way to write a case statement (expression)
I have written as follows:
CASE WHEN :1='bbbbb' THEN B. DEPTID = 50000,563000,56400,56450,56500,58400
41500
ELSE CASE WHEN :1= 'aaaaa' THEN B. DEPTID= 41000,55300,53001,55303,56900,57200,57220,57500,58000,41500
END
The:1 is a prompt that the user enters to run the query, then depending on the usernumber the query only runs data for specified departments .
So to be clear - if usernumber = aaaaa then run only departments 410000, 530001, etc.
Thank for your help,
Syhctl
0
Comment
Question by:syhctl
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 14

Expert Comment

by:ragoran
ID: 19993556
To facilitate maintenance, I will build a simple table having Usernumber and department number.  Based on the example above, there wiil be 7 rows or usernumber aaaaa and 10 for bbbbb.

You can then join this table on the other query, filtering on the desired usernumber.

The day you have more departments/usernumbers, you don't have to change your queries, just the content of this table.
0
 

Author Comment

by:syhctl
ID: 19993903
Unfortunately, I am in PeopleSoft the tables already exist and would be unable to build without joining multiple tables to filter the ones that I need.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 19993930
Are you in MS Access or SQL Server ?
0
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!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19993999
peoplesoft -> looks like oracle.

anyhow, if it's oracle or sql server, the CASE syntax won't work for the where like that.

what about this:
WHERE ( :1 = 'bbbb' AND  B.DEPTID IN ( '50000','563000','56400','56450','56500','58400', '41500' ) )
   OR ( :1 = 'aaaa' AND  B.DEPTID IN ( '41000','55300','53001','55303','56900','57200','57220','57500','58000','41500' ))

0
 
LVL 14

Accepted Solution

by:
ragoran earned 2000 total points
ID: 19994002
Or maybe Oracle... hmm

Since your example is based on CASE I will assume either SQL Server or Oracle as this instruction doesn't exist in Access

In the Where clause, you should ave something like:

( CASE :1
WHEN 'bbbbb' THEN B. DEPTID in (50000, 563000, 56400, 56450, 56500, 58400, 41500)
WHEN 'aaaaa' THEN B. DEPTID in (41000, 55300, 53001, 55303, 56900, 57200, 57220,  57500, 58000, 41500)
else 0
END )
0
 

Author Comment

by:syhctl
ID: 19994052
PeopleSoft is Oracle db.  I will try the last comment and let you know if it works
Thanks for your help.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 19997398
This should work.

WHERE B.deptid IN
(CASE WHEN :1 = 'bbbb'
            THEN ('50000','563000','56400','56450','56500','58400', '41500')
            WHEN :1 = 'aaaa'
            THEN ('41000','55300','53001','55303','56900','57200','57220','57500','58000','41500') END)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19997671
@jinesh_kamdar:
your suggestion is invalid syntax...

@syhctl:
please try my suggestion.
0
 

Author Comment

by:syhctl
ID: 19998805
Thanks for all the help -- the accepted solution worked best with the Oracle database.
Syhctl
0
 

Author Comment

by:syhctl
ID: 19998817
I have one more question on the same topic will submit another question.
Thanks again,
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20008041
angel - You are right, thats invalid syntax.

ragoran / syhctl - Can you illustrate how the accepted solution works in Oracle. Especially the ELSE 0 part does not make any sense to me. I tried with a simple query and it didnt work for me!

select * from dual
where (case dummy when 'X' then dummy in ('X') when 'Y' then dummy in ('Y') end)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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