| PL/SQL create SQL based on user choices [message #197570] |
Wed, 11 October 2006 19:41  |
bentonOracle
Messages: 5
Registered: October 2006
|
Junior Member |
|
|
Hello,
I am currently creating an application in HTMLDB and have used SQL extensively throughout for the display of reports. The user views the reports based on a number of Drop down lists (LOV). The 'return value' of the LOV is used to filter the report. This works great but is very ordered in the way it operates as the user cannot change the sequence of the drop down lists.
For example this is a simplified version of the SQL for the reports,
1 Filter
The SQL for report 1 would look like this,
SELECT A.FIELD1, A.FIELD2, A.FIELD3
FROM #OWNER#.VIEWNAME A,
WHERE A.FIELD1 = :P10_FIELD1
2 Filter's
The SQL for report 2 would look like this,
SELECT A.FIELD1, A.FIELD2, A.FIELD3
FROM #OWNER#.VIEWNAME A,
WHERE A.FIELD1 = :P10_FIELD1
AND A.FIELD2 = :P10_FIELD2
3 Filter's
The SQL for report 3 would look like this,
SELECT A.FIELD1, A.FIELD2, A.FIELD3
FROM #OWNER#.VIEWNAME A,
WHERE A.FIELD1 = :P10_FIELD1
AND A.FIELD2 = :P10_FIELD2
AND A.FIELD3 = :P10_FIELD3
If the user wanted to filter only by FIELD2 or FIELD3 there is no opportunity to do this. I could create reports with every combination of LOV's used and not used and this would be a good option if there where only 3 LOV's but there can be 6 and more LOV's to filter by and the combinations are too numerous.
What I would like to have is the ability to return a report based on the LOV filters the user chooses not the ones I have defined for them. So I would have only one report. And the filters for the SQL would change, based on what LOV the user hits on the page.
Is it possible to create one report that will include the LOV filters the user chooses, I thought PL/SQL would be the answer using a number of conditional statements. Could someone give me some indication how I would go about this?
Thank You for any Assistance
|
|
|
|
| Re: PL/SQL create SQL based on user choices [message #197575 is a reply to message #197570] |
Wed, 11 October 2006 20:43  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Hi and welcome to OraFAQ.
You could put pull-downs for all 3 options, but make them optional. Add a process to validate that the user selected at least one, and then write your SQL like this:
WHERE (field1 = :P10_FIELD1 OR :P10_FIELD1 IS NULL)
AND (field2 = :P10_FIELD1 OR :P10_FIELD2 IS NULL)
AND (field3 = :P10_FIELD1 OR :P10_FIELD3 IS NULL)
Note that performance will be poorer with this type of query because the optimizer cannot easily choose the right index.
Ross Leishman
[Updated on: Wed, 11 October 2006 20:43] Report message to a moderator |
|
|
|
| Re: PL/SQL create SQL based on user choices [message #197590 is a reply to message #197575] |
Wed, 11 October 2006 22:55 |
bentonOracle
Messages: 5
Registered: October 2006
|
Junior Member |
|
|
|
I have attempted to approach it by using the method you have suggested but the problem is, the report wants to show the records for a null value as well as the LOV value. I wondered if PL/SQL could help by allowing conditional statements to be used and build up the SQL based on the user choices, one of the things that I am having trouble with is seeing the results from a PL/SQL statement.
|
|
|
|
| Re: PL/SQL create SQL based on user choices [message #197625 is a reply to message #197575] |
Thu, 12 October 2006 01:47 |
bentonOracle
Messages: 5
Registered: October 2006
|
Junior Member |
|
|
Hello Again,
I have attempted to pursue the PL/SQL option and have come up with
something below
DECLARE
v_empno number;
v_ename varchar2(2000);
v_deptno varchar2(2000);
v_job varchar2(2000);
v_employee_no VARCHAR2(2000) := 'AND EMPNO = :P10_EMPLOYEE_NO';
v_employee_job VARCHAR2(2000) := 'AND JOB = :P10_EMPLOYEE_JOB';
BEGIN
SELECT EMPNO, ENAME, DEPTNO, JOB
INTO v_empno, v_ename, v_deptno, v_job
FROM EMP
WHERE empno > 0;
IF :P10_EMPLOYEE_NO > 0 THEN
v_employee_no;
ELSIF :P10_EMPLOYEE_JOB > 0 THEN
v_employee_job;
END IF;
END;
but gives the following error message
"Missing IN or OUT parameter at index:: 1
Vendor code 17041
"
Am I on the right track and if I am and even if I am not how do I get the results printed to screen.
|
|
|
|
| Re: PL/SQL create SQL based on user choices [message #197629 is a reply to message #197590] |
Thu, 12 October 2006 02:01 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Thats a bit over my head. The REPORT wizard doesn't offer a PL/SQL option in my version; just SQL. Since its just SQL, you can't use Dynamic SQL (easily)
I don't get the problem with my suggestion. Say a row has a NULL field1, and you run a report for FIELD1 = 'X':
WHERE (field1 = :P10_FIELD1 OR :P10_FIELD1 IS NULL)
=> NULL = 'X' OR 'X' IS NULL)
=> UNKNOWN OR FALSE
=> UNKNOWN
There is a way to use Dynamic SQL, but its butt-ugly.
In order of execution:
- A PROCESS in HTML/DB linked to the GO button would set global package variable my_package.glob_field1 := :P10_FIELD1. Same for the other fields.
- The report is coded simply as SELECT * FROM my_view. ie. no WHERE clause
- The view is defined on the database as
SELECT *
FROM TABLE(my_package.my_pipe_func(
my_package.get_field1()
, ...
) - my_package.get_field1() etc are packaged functions that return the values of global package variables my_package.glob_field1 etc.
- my_package.my_pipe_func is a pipelined function (see the PL/SQL doco) that constructs a dynamic SQL (EXECUTE IMMEDIATE) based on the package globals passed in, and pipes back the results to the view, which passes it back to HTML/DB
Simple, isn't it? I wouldn't do this if I were you. No one will ever understand what the hell you were doing.
Ross Leishman
|
|
|
|
| Re: PL/SQL create SQL based on user choices [message #197653 is a reply to message #197629] |
Thu, 12 October 2006 03:27 |
bentonOracle
Messages: 5
Registered: October 2006
|
Junior Member |
|
|
Hi Again,
I have attached a application export named f143_dos.sql which contains a quick and dirty example of the sort of thing I am trying to accomplish. It uses the oracle table EMP. I have created a report and used the IS NULL lines of code and you will be able to see what they are doing. Do you mind having a look at it and giving me any feed back, you mentioned something about it being a bit over your head well I feel like I am floundering at the moment.
-
Attachment: f143_dos.sql
(Size: 313.41KB, Downloaded 1434 times)
[Updated on: Thu, 12 October 2006 03:49] Report message to a moderator |
|
|
|
| Re: PL/SQL create SQL based on user choices [message #197658 is a reply to message #197653] |
Thu, 12 October 2006 03:53 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The LOV has to return a value - it cannot return NULL. At the moment, it returns '%' which is non-numeric - hence the error.
- Go to ITEMS/P1_DEPTNO, in the LOV section set the "Null Return Value" to -1.
- Do the same for EMPNO.
- Update the report DYNAMIC REPORT SQL to
select DEPTNO, EMPNO, ENAME, JOB
from EMP
where JOB = :P1_JOB
and (DEPTNO = :P1_DEPTNO or :P1_DEPTNO = -1)
and (EMPNO = :P1_EMPNO or :P1_EMPNO = -1)
It works for me.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
