We want to use a user input as a column name in APEX.
For e.g user will enter "ALLOWABLE_AMOUNT" then the query will be as follows :
select Rule,rule_name,rule_desc,"User Input" from rule_dim
where "User_input" > 100
So here the User_input will be substitued with Allowable_amount. Is this doable using any bind/substitution variables ? I tried ":P2_USER_VARIABLE" and "&P2_USER_VARIABLE." but did not work.
I think you need use function that returns your query as report source.
This is possible for classic report.
For interactive report you need use bit different approach like this
Its possible to do like that.
Create an item P_USER_VARIABLE in the html region.
Also create an button as submit with action when button clicked as 'Submit page'.
Then create the report with the query below,
select Rule,rule_name,rule_desc,:P2_USER_VARIABLE "User Input" from rule_dim
where :P2_USER_VARIABLE > 100
Try this i think that it might help you.
You do that with an Interactive Report and a Dynamic Action.
I'll assume that you're using APEX 4.2
Enter something into the USER VARIABLE field and select something else on the page. Watch the last column update to that value.
Background -- what I'm thinking: Allow the user to input a COLUMN name (SAL or COMM) and compare that column with a user-entered AMOUNT to retreive employees with say, SAL > 1000 ... or ... COMM > 100.
I tried this (above technique) with the EMP table and it doesn't work for me so far. I don't see how it can work this way. The variable -- :P2_VARIABLE in your case -- is going to be treated as constant data and not a column name.
How about a specific example?
SELECT * FROM (SELECT EMPNO, ENAME, JOB, MGR, :P1_COLUMN AS USER_INPUT FROM EMP) WHERE USER_INPUT > :P1_AMOUNT
If : P1_COLUMN equals SAL, then the inner SELECT has SAL for USER_INPUT for each row and the WHERE clause compares "SAL" to :P1_AMOUNT. I want the values of the SAL column compared -- not the literal "SAL" compared.
It doesn't work -- or am I missing something here.
You could create P1_COLUMN as a drop-down list that contains the values "SAL" and "COMM". And P1_AMOUNT would just be a text field to accept some number.
Something like this might work:
SELECT * FROM (SELECT EMPNO, ENAME, JOB, MGR, :P1_COLUMN AS USER_INPUT FROM EMP)
CASE WHEN USER_INPUT = 'SAL'
THEN SAL > NVL(:P1_AMOUNT, '0')
WHEN USER_INPUT = 'COMM'
THEN COMM > NVL(:P1_AMOUNT, '0')
END = 1
I haven't tested the example... just another idea...
Thanks. Yes, that -- or something very close -- will work. Good idea.
But I was intrigued that perhaps you'd come upon a way to make the query "dynamic" without having to return a query string. But alas, that's likely impossible. This solution "simulates(?)" a dynamic query but only by hard-coding all the selections. Technically, I suppose it's a "scalable" solution but not one I would want to use for numerous selections.
All of this seems intriguing but I must ask:
What is wrong with using an Interactive Report and telling the user "go hit 'action'->'filter'..." ??
If you still need that feature, I'd see if it is possible to dynamically add a filter to the IR based on a form entry.
I would research that route prior to making a dynamic SQL statement.
Thanks. Just had a quick question. When i implemented the steps suggested by you ,everything looks great but in step # 4 , where its pulling dynamic column in the SQL its showing me Column name instead of column value. For e.g if Sales is the column name its showing "Sales" in the output instead of value like 100,200,etc.
Can you please advice where i am going wrong ?
Ah, sorry. You have followed my instructions properly. However, I interpreted your question incorrectly and provided an example of creating an input field that's used to filter a report and is displayed as a column in the report.
At the risk of SQL injection attacks, you could try:
jariola's answer above will be more helpful.