Selecting a column in a where statement

I know someone can do this... just not me. I want to have a select list that will return a "where" statement I can use in my report query. For example, I have a column in my table called "Condition A" that is either a 1 or 0. I have another column in my table called "Condition B" that is either a 1 or a 0. I want a select list called "Options" that will let the user pick their own where statement. For this example, the options would be: Where Condition A = 1 Where Condition B = 1 Null

Then in my report query, I could use that returned value so that my query could say: select * from mytable &Options.

Something like that. I hope I'm being clear. I've tried several different ways of making it work but to no avail. Please help! Thank you!

Solution

Here is an example. It's a report on the EMP table with a select list that returns different where clauses.

select list page item

Page item P273_WHERE_CLAUSE, type "Select List", values:

report

Interactive report of type "Function Body returning SQL Query". This is needed since the SQL needs to be generated dynamically.

Source:

DECLARE
    l_query varchar2(4000);
    l_where_clause varchar2(4000); 
BEGIN
    -- need default value so report compiles correctly
    l_where_clause := CASE WHEN :P273_WHERE_CLAUSE IS NULL THEN '1 = 0' ELSE :P273_WHERE_CLAUSE END;
    l_query := 
    q'!select
                      EMPNO,
                      ENAME
                   from
                      EMP
                   where 
                      %0!';
    -- log this message in apex_debug 
    apex_debug.info(
       p_message => q'#chuckle debug: query: %0#',
       p0        => apex_string.format(l_query, l_where_clause));
    return(apex_string.format(l_query, l_where_clause));
END;

Set "Page Items to Submit" (for the dynamic action in the next step)

--update

Note the debug message. When this page is run in debug mode, this message will be visible in the debug messages (just search for "chuckle debug") . This allows for faster debug.
I'd suggest getting familiar with the APEX_STRING api - the FORMAT method can greatly simplify your code (as shown in this blog). In the sample code above the %0 is a substitution variable (both in APEX_STRING.FORMAT and APEX_DEBUG.MESSAGE)

dynamic action

Create a dynamic action on change of P273_WHERE_CLAUSE with a refresh action on the interactive report.

security notes

While this works, this has a big security flaw. The where clause is visible in the page html source. A user can manipulate the select list where clause in the DOM and get other results than what the developer intended. If this is for a production environment, the where clause should be stored in a table and the return value of the select list should just be the primary key of the row containing the where clause. Then in the report code, retrieve the where clause for the select list value from the table. That way the actual SQL is never visible to a user.