This discussion is archived
11 Replies Latest reply: May 17, 2013 1:00 AM by 1009514 RSS

Dynamic Where Clause

807466 Journeyer
Currently Being Moderated
Hello -

I did a quick search of the forum but didn't see the answer to this question. Perhaps I am searching for the wrong keywords.

I am wanting to dynamically build my where clause for a report (either IR, or SQL) based on a page item.

Sample SQL
SELECT ...
FROM ...
WHERE dept_id = 123
If :P1_ITEM = <default_value> then SQL remains as shown above.
IF :P1_ITEM != 'Default' Then where clause is set to something like:
SELECT ...
FROM ...
WHERE dept_id = 123
  AND emp_id = 456
My first thought was to get another page item (P1_WHERE_CLAUSE) and set it to the statement of the 'additional where' (AND emp_id = 456) via a page computation that was conditional to the P1_ITEM. Then, in my SQL for the report I would reference the item, such as:
SELECT ...
FROM ...
WHERE order_id = 123
  &P1_WHERE_CLAUSE.
However, when I try to do this, my report can't parse the SQL.

So, can I build a dynamic where clause for my report or not? If so, how?

Many thanks

Austin
  • 1. Re: Dynamic Where Clause
    TexasApexDeveloper Guru
    Currently Being Moderated
    For standard reports try using a function returning sql: Pl/Sql Function Body Returning Sql Query Problem

    For IR's try looking at this article: http://www.oracleapplicationexpress.com/tutorials/71

    Thank you,

    Tony Miller
    Webster, TX

    Follow your passion; the rest will take care of itself.

    JMS

    If this question is answered, please mark the thread as closed and assign points where earned..
  • 2. Re: Dynamic Where Clause
    807466 Journeyer
    Currently Being Moderated
    Thank you Tony.

    Anything out there about Charts? Or could i use something similar to the IR tutorial?

    Austin
  • 3. Re: Dynamic Where Clause
    TexasApexDeveloper Guru
    Currently Being Moderated
    You can use the same techniques for charts too!!!: http://apex.oracle.com/pls/otn/f?p=37848:11:1481289672515384

    Thank you,

    Tony Miller
    Webster, TX

    There are two kinds of pedestrians -- the quick and the dead.

    If this question is answered, please mark the thread as closed and assign points where earned..
  • 4. Re: Dynamic Where Clause
    510477 Pro
    Currently Being Moderated
    An alternate method is the use of a DECODE command in the where clause. This method works great - all you do is make sure that the default value matches to return all the records:

    Example:
    SELECT ...
    FROM ...
    WHERE dept_id = 123
      AND emp_id = 456
    becomes
    SELECT ...
    FROM ...
    WHERE dept_id = DECODE(:P1_DEPT,0,dept_id,:P1_DEPT)
      AND emp_id = DECODE(:P1_EMP,0,emp_id,:P1_EMP)
    0 is the default value. I use this frequently with LOV's where the Null value reads '-- ALL --' to the user and returns 0. It's not technically a dynamic WHERE clause, but in gets the job done.
  • 5. Re: Dynamic Where Clause
    TexasApexDeveloper Guru
    Currently Being Moderated
    I believe the poster wants the option of NOT always having the dept & emp id's in where clause, thus you would have to build the select as a string to meet the requirements..

    Thank you,

    Tony Miller
    Webster, TX

    While it is true that technology waits for no man; stupidity will always stop to take on new passengers.

    If this question is answered, please mark the thread as closed and assign points where earned..
  • 6. Re: Dynamic Where Clause
    510477 Pro
    Currently Being Moderated
    Whatever works. If you dynamically build the WHERE clause, there will be one more computation or process that has to fire in the correct sequence and return the proper syntax to the item before the query can be processed. This means additional background overhead as the page gets built and processed. The DECODE works well for simple solutions. The dynamic build probably works better for more complex things where the time to build the custom WHERE clause is more than made up by the query execution time. Just a different approach.
  • 7. Re: Dynamic Where Clause
    807466 Journeyer
    Currently Being Moderated
    Thank you blarman74.

    The example i gave simply illustrates what I am trying to do. If it was as simple as changing values, it would be done. Perhaps a better example would that of changing an AND to an OR based on a page item. Decode statements won't help in that situation. I am actually looking at changing the entire syntax of the where clause (which columns are filtered on in the where clause) based on specific page items (and possibly removing the where clause entirely). From my perspective, i'll need to use collections.

    Austin
  • 8. Re: Dynamic Where Clause
    510477 Pro
    Currently Being Moderated
    Thanks for the clarification. The OR example rules out the DECODE. So much for simple ;)
  • 9. Re: Dynamic Where Clause
    vee Guru
    Currently Being Moderated
    SELECT ...
    FROM ...
    WHERE dept_id = 123
    AND (   :P1_ITEM = <default_value>
           OR (       :P1_ITEM != 'Default' 
                  AND emp_id = 456
                )
           )
    Ofcourse if the processing involved to get to each of those cases is more complex than the actual clauses itself, dynamic SQL's would be a better option.But for most practical purposes, normal SQL can handle them just well(albeit some complexity)
  • 10. Re: Dynamic Where Clause
    TexasApexDeveloper Guru
    Currently Being Moderated
    Thread's closed and people still want to add-on..... User wanted a simple solution....

    Thank you,

    Tony Miller
    Webster, TX
  • 11. Re: Dynamic Where Clause
    1009514 Newbie
    Currently Being Moderated
    I'd like to add another solution which works for me:
    I'll use the following example:
    SELECT ...
    FROM ...
    WHERE order_id = 123
    &P1_WHERE_CLAUSE.
    Yes, it's not parsable, because it is not valid expression, but if I change it to
    SELECT ...
    FROM ...
    WHERE order_id = 123
    /* &P1_WHERE_CLAUSE. */
    and the initialization of P1_WHERE_CLAUSE is something like:
    :P1_WHERE_CLAUSE := '*/ AND emp_id = 456 /*';
    then everything works fine. No SQL parse error and the statement is executed correctly.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points