Search code examples
oracle11goracle-apex

In Apex, how do you use the return value from a list of values in your query's "where" statement

I have a select list in Apex called :P1_ACCOUNTS. The return value is an "and" statement. For example, if the user selects "30" in the select list then the return value is "and pd_status >= 30".

I used to be able to use that in the query for my report like so: select count(*), pd_status from mytable &P1_ACCOUNTS.

Apex would interpret the &P1_ACCOUNTS. as "and pd_status >= 30". However, it doesn't work any more. Does anyone know how to make this work again? Or does anyone have a better option for me?

There is a catch to this: The AND statement needs to contain ">=" or "=" depending on the selection which is why I'm doing it this way. So if they select "30" it will return "and pd_status >= 30". But if they select "30 act", it should return "and pd_status = 30". The reason that makes a difference is because I can't use this in my query: "and pd_status = :P1_ACCOUNTS"... sometimes the sign needs to be "=" and sometimes ">="

Solution

  • I doubt this has ever worked in a sql statement in apex. In oracle, it has never been possible to use a complete statement (eg and pd_status >= 30) as a bind variable.

    To make this work, use reports with as source "Function returning SQL Query". In that type, the query is generated in a function, so the page item value would be concatenated to the sql statement. Also, it needs to be the bind variable syntax in pl/sql (:P1_ACCOUNTS), never the substitution string syntax (&P1_ACCOUNTS.).

    This is example code for "Function returning SQL Query"

      RETURN 'select count(*), pd_status from mytable where 1 = 1 '|| :P1_ACCOUNTS;

    However, there is another technique that allows you to use plain sql. No concatenation is needed. Here is an example on the emp table.

    The select list P1_SAL_VALUE has 2 values:

    • one for SALARY = 3000, return value eq-3000
    • one for SALARY >= 3000, return value gte-3000

    'eq' stands for equal, 'gte' stands for greater than or equal. Create any value you want for other operators.

    Split the return value into the numeric value and the operator using REGEXP_SUBSTR

    ('eq-3000', '[^-]+', 1, 1) returns the string eq and ('eq-3000', '[^-]+', 1, 2) returns the value 3000

    so the resulting SQL will be:

    select * from emp 
where 
(regexp_substr (:P1_SAL_VALUE, '[^-]+', 1, 1) = 'eq' AND sal = regexp_substr (:P1_SAL_VALUE, '[^-]+', 1, 2)
 OR
regexp_substr (:P1_SAL_VALUE, '[^-]+', 1, 1) = 'gte' AND sal >= regexp_substr (:P1_SAL_VALUE, '[^-]+', 1, 2))
;