Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » PL/SQL create SQL based on user choices
PL/SQL create SQL based on user choices [message #197570] Wed, 11 October 2006 19:41 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
ie. the row should not return.

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 Go to previous messageGo to next message
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 338 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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: PL/SQL create SQL based on user choices [message #197827 is a reply to message #197658] Thu, 12 October 2006 18:21 Go to previous message
bentonOracle
Messages: 5
Registered: October 2006
Junior Member
Hi,

Thank You very much for your assistance, that worked for me too.

Ben
Previous Topic: Temporary Table Scope in OAE
Next Topic: mod_plsql
Goto Forum:
  


Current Time: Sun Dec 11 06:02:26 CST 2016

Total time taken to generate the page: 0.05942 seconds