Dynamic Column on SQL doesn't work (APEX,Interactive Report)

Question

I tried to implement a page on APEX(19.2) , where the user has to make an input , which is the column name. This input shall restrict the select statement on the where clause and is the following:

select * FROM UEBERSICHT where
:P904_COLUMN = :P904_AUSDRUCK;

:P904_COLUMN and :P904_AUSDRUCK; are both APEX items, which is needed for the user input.

When I write the column name instead of :P904_COLUMN, I get an output, otherwise not. But as the headline says, I want to implement a dynamic column.

I also tried it with PL/SQL , which returns a SQL - statement like the following:

declare
statement varchar2(4000);

begin


statement:= 'SELECT * FROM UEBERSICHT where
 :P904_COLUMN = :P904_AUSDRUCK;';

return statement;
end;

Another approach was , to save the input on a variable first and write the variable name into the SQL - Statement:

 declare
statement varchar2(4000);
spalte varchar2(50);
begin

if :P904_COLUMN = '"Gesellschaft"' then spalte := '"Gesellschaft"'; end if;    
statement:= 'SELECT * FROM UEBERSICHT where
 '||spalte||' = :P904_AUSDRUCK;';

return statement;
end;

Here i get this syntax error, which shouldn't appear normally: "ORA-20999: Parsing returned query results in "ORA-20999: Failed to parse SQL query!

ORA-06550: line 5, column 6: ORA-00936: missing expression

"."

How can I solve this problem ?

PS: Yes I am submitting all APEX items.

Update: The debug shows me that I get the Input, but the interactive Report doesn't give me any output though.

psaraj12 psaraj12 · Accepted Answer · 2020-04-24T13:09:18

IF all the columns in :P904_COLUMN is of same data type you can use something like below where A and B are column names

 select * FROM UEBERSICHT where
 DECODE(:P904_COLUMN,'A',A,'B',B) = :P904_AUSDRUCK;

Dynamic Column on SQL doesn't work (APEX,Interactive Report)

1 Answers