| Needs a Solution [message #343347] |
Wed, 27 August 2008 00:39  |
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  |
 |
Littlefoot
Messages: 21808
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') |
|
|
|
|
|
| Re: Needs a Solution [message #346555 is a reply to message #343347] |
Mon, 08 September 2008 19:15 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
