2

For a dummy test, I want to show a list of employees in a web form.

There is a drop down on the web form that contains a short list of departments, like this:

All Depts
Sales Dept
Marketing Dept
Communication Dept
HR Dept
Finance Dept
IT Dept

The drop down item of All Depts has a value of 0.

The following fiddle shows you what I am trying to do:

http://sqlfiddle.com/#!4/59d1f/2

I know I can do this:

IF (deptid = 0) THEN
   select firstname, lastname from employees;
ELSE
   select firstname, lastname from employees where deptid = :p_deptid
END IF;

But my real situation has a much more convoluted select query that involves joins of multiple tables. So, I don't wanna clutter up my script with repetitive codes.

Can I achieve my goal using CASE WHEN? Or do I have to use dynamic SQL?

Thanks.

Stack0verflow
  • 1,148
  • 4
  • 18
  • 42

1 Answers1

2
 SELECT firstname, lastname
 FROM employees
 WHERE 0 = :p_deptid
 OR dept_id = :p_deptid
symcbean
  • 47,736
  • 6
  • 59
  • 94