We help IT Professionals succeed at work.

SQL - CASE STATEMENT (EXPRESSION)

syhctl
syhctl asked
on
409 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
Comment
Watch Question

Commented:
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.

Author

Commented:
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.

Commented:
Are you in MS Access or SQL Server ?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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' ))

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
PeopleSoft is Oracle db.  I will try the last comment and let you know if it works
Thanks for your help.
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)
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
@jinesh_kamdar:
your suggestion is invalid syntax...

@syhctl:
please try my suggestion.

Author

Commented:
Thanks for all the help -- the accepted solution worked best with the Oracle database.
Syhctl

Author

Commented:
I have one more question on the same topic will submit another question.
Thanks again,
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)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.