Needs a Solution [message #343347] |
Wed, 27 August 2008 00:39 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
myclassic
Messages: 136 Registered: December 2006 Location: Pakistan
|
Senior Member |
|
|
Dear
I have got three type of employee types in a table
Administration
Doctors
Staff
...... Administration and Doctors are considered as Officers
and Staff is Staff.
I want to generate a report of employees while passing a parameter of either Staff or Officer.
Staff is no problem. But Officers are devided into two groups (administration and Doctors)......
how to get this report????????
remember parameter will be only one(Staff or Officer)....
regards.
|
|
|
|
Re: Needs a Solution [message #344420 is a reply to message #343347] |
Fri, 29 August 2008 14:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Let's assume that table looks like SQL> select * from test order by job;
ENAME JOB
---------- --------------
FORD Administration
SCOTT Administration
BLAKE Doctor
JONES Doctor
MILLER Staff
KING Staff
TURNER Staff
ADAMS Staff
JAMES Staff
MARTIN Staff
WARD Staff
ALLEN Staff
SMITH Staff
CLARK Staff
Create two parameters; first one will be an "ordinary" parameter (let's call it "par_job", character, length = 1), while another one will be lexical parameter (let's call it "lex_where" (character, length = 100)).
Query will look like this:select employee_name, job
from test
&lex_where
Lexical parameter will get its value in the AFTER PARAMETER FORM trigger, which will look like this:
if :par_job = 'S' then
:lex_where := 'where job = ''Staff''';
elsif :par_job = 'O' then
:lex_where := 'where job in (''Administration'', ''Doctor'')';
end if;
Run the report; if "par_job" parameter value is 'S', you'll get all employees whose job column equals "Staff"; if parameter value is 'O', you'll get Administrators and Doctors. This part of the lexical parameter value - regarding values you've mentioned - might be of two kinds: the first one is already suggested:where job in ('Administration', 'Doctor') and the second one might beUse whichever you prefer.
|
|
|
|
Re: Needs a Solution [message #346555 is a reply to message #343347] |
Mon, 08 September 2008 19:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dude4084
Messages: 222 Registered: March 2005 Location: Mux
|
Senior Member |
|
|
Wonderful work by Littlefoot.
But i think it can be achieved like this as well (without using lexical parameter).
select employee_name, job
from test
where ((:par_job='O' and JOB!='Staff') or
(:par_job='S' and JOB='Staff') )
-Dude
|
|
|
|