This content has been marked as final. Show 9 replies
Is it a select list with submit ?
Use PL/SQL function returning a SQL query.
In the PL/SQL function body build your report query dynamically and return the whole query.
Using this you will be able to evene change your from , where, select & order by clauses.
you might try something like
Select a, b, c from sample order by decode(:P1_ORDER_BY,'COLUMN1',a,null), decode(:P1_ORDER_BY,'COLUMN2',b,null), decode(:P1_ORDER_BY,'COLUMN3',c,null) ;
Yes its a select item with page submit as action
With this example, i guess the select item is not in use?
And i tried with decode function but its not working here.
Edited by: Krishna Vashistha on Aug 24, 2012 4:48 PM
I agree 'PL/SQL function body returning SQL query' is a valid option available, but is there no other way out in APEX to implement with 'SQL Query'?
I don't think so with my limited knowledge.
Of course the select item is in use.
It should submit the the page on select so you can retrieve the current value in you report select statement.
table: create table sample ( a varchar2(30) , b varchar2(30) , c varchar2(30) ); insert into sample values('a','z','k'); insert into sample values('b','y','d'); insert into sample values('c','x','a'); insert into sample values('d','w','b'); commit; Select List: P2_ORDER_BY STATIC:Order by A;A,Order by B;B,Order by C;C Report select: select a,b,c from sample order by decode(:P2_ORDER_BY,'A',a,null) , decode(:P2_ORDER_BY,'B',b,null) , decode(:P2_ORDER_BY,'C',c,null)
Edited by: telemat on Aug 24, 2012 1:50 PM
Great! It worked :)
Thanks so much