Ben_962957 wrote:What do you mean by "master query"? How do you see this working? What Oracle/APEX feature(s) are you thinking about?
I have a multi-region APEX report page where each region is reporting/charting different parts of the same SQL query.
In my current configuration, each report/chart has it's own variant of the same SQL query.
Is there some way to have a master query for a page and then have each report/chart region just reference the result set from that master query?
Also, the query is a little complex (speed might be an issue later).Normally I'd say this was a possibility. One of the few cases where it makes sense to use a global temporary table with APEX, or APEX collections if not restricted by the limited column options available in the <tt>apex_collections</tt> view. However...
If using a master query is not possible or too cumbersome, how about using a page process to populate a local table with the results of the master query, then the page reports/charts can just reference the local table (instead of a giant query).
More specifics:...this could be an issue with any approach.
I'm running APEX 4.0 on an oracle 11g DB.
I have a DB link to an oracle 10g DB where most of the data actually resides.
My report page is a parametrized report: user picks a date range, a part#, and a machine#, then 4+ separate (but similar) queries run and the data is displayed in 4+ different regions.How much data needs to be pulled from the remote DB? Will multiple users be accessing the same data? Where should the "big messy join" occur: remotely or locally? Do the parameters identify/restrict data on the remote DB?
The queries look functionally like this:
SELECT lot_id, part_id, machine_id, record_date, avgX, rangeX, mR_X, UCL1, TGT1, LCL1, ... FROM <...big messy multi-join> WHERE <parametrized where clause>;
1st chart uses: lot_id, avgX, TGT1, UCL1, LCL1The APEX best practices here are the basic ones for using DB links and reusing SQL. Minimize the amount of data that needs to be transferred over the link, or replicate it locally. Create a view containing the common SQL so it only needs to be maintained in one place.
2nd chart uses: lot_id, mR_X, TGT2, UCL2, LCL2
3rd chart uses: lot_id, rangeX, TGT3, UCL3, LCL3
1st report uses: all columns except TGT#, UCL#, LCL#
2nd report uses: statistics on all columns in first report (avg, stddev, and various combinations of avg and stddev)
If I ever want to update this query, I have to go to 4+ different places in my apex page to update.
Is there a better way? either page-based master query idea? or page-process populates local table with master query results idea? or something else?
What are Apex "best practices" for this kind of scenario: an apex page with multiple regions all reporting/charting different parts of the same query.