Hi,
I'm trying to produce a report using a dynamic sql query, and I'm trying to construct this dynamic sql query based on parameters chosen by users. I've created an example app using "car" data on apex.com to illustrate what I'm trying to do. Here are the login parameters:
Website: http://apex.oracle.com
Workspace: MYHOSTACCT
Username: DEVUSER1
Password: MYDEVACCT
More details...
The report I'm trying to produce is on table "cars_2012." And, I've got a page that contains a tabular form where users can select which columns they want to include and which values to filter on using popup shuttle lists that return values back to text fields in the parent form. The initial values for the report parameters in the tabular form are based off of table "rpt_build."
The users check the checkboxes for columns they want to include, and search for values to filter on using modal popup shuttle lists.
I've got all of the functionality with the shuttle lists and checkboxes working correctly with JavaScript, now I'm just having trouble constructing the dynamic sql statement in the processing code. One of the main problems I'm having is trying to get the "where clause" values, which are stored in an array in the text fields (f05), into a comma-delimited "in" clause. The values return one by one, but I can't get them in a list. I've tried using apex_util.table_to_string and string_to_table among other things to no avail.
My pseudo-code for the dynamic sql statement should be something like the following:
select <columns that are checked by user>
from <object name>
where <for each column, values contained in the text field>
group by <columns that are checked by user>
An example result:
select car_make,car_type,sum(qty_sold)
from cars_2012
where car_make in ('Toyota','Honda')
and car_color in ('Blue','White')
group by car_make,car_type;
Could someone please take a look at my example and help me? I'm pretty sure I need to use collections of some sort, but just don't know how exactly I should implement them.
Update... I've added much of the functionality to the pl/sql process that generates the dynamic sql. I still need help with the WHERE clause and with saving the checkbox values and text field values after the process is run. Hopefully, someone can guide me in the right direction.
Thanks in advance!
Mark
Edited by: user455268 on Mar 27, 2012 6:43 PM