This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jun 8, 2012 3:57 AM by em200281 RSS

SQL too big for region source

752894 Newbie
Currently Being Moderated
Hi All,

I am building an interactive report and my sql is 1500 lines and the region source is throwing an error saying its a bad request and i exceeded the limit.

I thought of creating a view. But I have few page items which I am using in this sql multiple times (not just in the WHERE conditions).

Can anyone suggest how to fix this?

Version - 4.1, DB - 10g

Thank you all in Advance,
Daniel
  • 1. Re: SQL too big for region source
    Bob37 Pro
    Currently Being Moderated
    How about after header process which is a PL/SQL process that would run the query and put the results into a collection, then your interacvive report would query the collection columns.

    If the report is reliant on page items that aren't available until the user fills them in, this could be an after submit process, and branch back to the same page. The interactive report would run if the collection was populated, otherwise not.

    Edited by: Bob37 on May 1, 2012 12:42 PM
  • 2. Re: SQL too big for region source
    TexasApexDeveloper Guru
    Currently Being Moderated
    If you move you code to a package and then use a pipeline function to return data to your report you can accomplish this easily! See this link for how to accomplish this:

    http://mikesmithers.wordpress.com/2012/02/22/getting-apex-to-play-with-ref-cursors/#more-1276

    Thank you,

    Tony Miller
    Dallas, TX
  • 3. Re: SQL too big for region source
    Bob37 Pro
    Currently Being Moderated
    That won't work for interactive report will it? No place to specify that your source is a package.
  • 4. Re: SQL too big for region source
    TexasApexDeveloper Guru
    Currently Being Moderated
    If you read the article.. You would see you are wrapping a select around a table type casting around a pipelined function, thus, you are doing a select that returns data from a function and treating is a table...

    I am using it for reports in an application now, so that there is less coded bundled in the application and instead is packaged in the database, for possible re-use if needed..

    Thank you,

    Tony Miller
    Dallas, TX
  • 5. Re: SQL too big for region source
    trent Expert
    Currently Being Moderated
    Hi,

    What about a parametized view?

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1448404423206

    or

    loading the item values once with the with clause.

    I'm not sure how the performance of the methods would stack up.... 1500 line query sounds pretty epic.
  • 6. Re: SQL too big for region source
    752894 Newbie
    Currently Being Moderated
    Hi,

    I apologize for not replying earlier. Working on the enahancements and my query is close to 2,000 lines now.
    Yes, the performance is definitely an issue. Its taking upto 5 min to see the result set.

    I will try Tony's suggesstion to create a package.
    I am not good with collections. so may be i will try it next.

    Either way, will it improve the performance?

    Thank you,
    Daniel
  • 7. Re: SQL too big for region source
    Bob37 Pro
    Currently Being Moderated
    I would say Tony's pipelined solution is the best performer. Oddly enough, we've used that for some classic reports, don't know I thought there'd be a problem with Interactive.

    Anyway it has fewer moving parts.
  • 8. Re: SQL too big for region source
    TexasApexDeveloper Guru
    Currently Being Moderated
    I will offer to help if you would like code assistance.. Post your select and lets see what it looks like... Or if you like, can you post a sample on oracles hosted site and we can go from there...

    Thank you,

    Tony Miller
    Dallas, TX
  • 9. Re: SQL too big for region source
    752894 Newbie
    Currently Being Moderated
    Hi Tony,

    Below is my query. What I am trying to do is. I am calculating the pass% for each weekday.
    The user selects a date(date picker item on UI). Irrespective of the DATE they selected, i should populate that week data (Sunday - Saturday) and the weekly total. These will be my columns.

    And I am posting only one set of my query. The other rows are populated based on different conditions. I have 21 such rows. I am doing UNION ALL to show these percentages in a report.


    SELECT

    +'% Pass',+
    trunc(case when completed.sunday_tot > 0  then ( closed.sunday_tot / completed.sunday_tot ) * 100
    else closed.sunday_tot * 100 END,2) sunday,
    trunc(case when completed.monday_tot > 0  then ( closed.monday_tot / completed.monday_tot ) * 100
    else closed.monday_tot  * 100 END , 2) monday,
    trunc(case when completed.tuesday_tot > 0  then ( closed.tuesday_tot / completed.tuesday_tot ) * 100
    else closed.tuesday_tot  * 100 END , 2) tuesday ,
    trunc(case when completed.wednesday_tot > 0  then ( closed.wednesday_tot / completed.wednesday_tot ) * 100
    else closed.wednesday_tot  * 100 END , 2) wednesday,
    trunc(case when completed.thursday_tot > 0  then ( closed.thursday_tot / completed.thursday_tot ) * 100
    else closed.thursday_tot  * 100 END , 2) thursday,
    trunc(case when completed.friday_tot > 0  then ( closed.friday_tot / completed.friday_tot ) * 100
    else closed.friday_tot  * 100 END , 2) friday,
    trunc(case when completed.saturday_tot > 0  then ( closed.saturday_tot / completed.saturday_tot ) * 100
    else closed.saturday_tot  * 100 END , 2) saturday,

    TRUNC( (closed.cnt/completed.cnt ) * 100,2) total_week
    FROM
    +(+
    SELECT
    MAX(type) type,
    MAX(SUM(CASE WHEN DAY = 'Sunday' THEN 1 ELSE 0 END )) sunday_tot,
    MAX(SUM(CASE WHEN DAY = 'Monday' THEN 1 ELSE 0 END)) monday_tot,
    MAX(SUM(CASE WHEN DAY = 'Tuesday' THEN 1 ELSE 0 END)) tuesday_tot,
    MAX(SUM(CASE WHEN DAY = 'Wednesday' THEN 1 ELSE 0 END)) wednesday_tot,
    MAX(SUM(CASE WHEN DAY = 'Thursday' THEN 1 ELSE 0 END)) thursday_tot,
    MAX(SUM(CASE WHEN DAY = 'Friday' THEN 1 ELSE 0 END)) friday_tot,
    MAX(SUM(CASE WHEN DAY = 'Saturday' THEN 1 ELSE 0 END)) saturday_tot,
    MAX(sum(closed_cnt)) CNT
    FROM
    +(+
    SELECT
    A.NUM num,
    B.OWNER OWNER,
    A.status STATUS,
    +'A' type,+
    case when to_date(B.start_dt,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-7, 'Sunday')  then 'Sunday'
    when to_date(B.start_dt,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-6, 'Monday')  then 'Monday'
    when to_date(B.start_dt,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-5, 'Tuesday')  then 'Tuesday'
    when to_date(B.start_dt,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-4, 'Wednesday') then 'Wednesday'
    when to_date(B.start_dt,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-3, 'Thursday') then 'Thursday'
    when to_date(B.start_dt,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-2, 'Friday') then 'Friday'
    when to_date(B.start_dt,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-1, 'Saturday') then 'Saturday'
    end Day,
    +1 closed_cnt,+
    B.start_dt,
    row_number() OVER ( partition by A.num order by rownum) rn
    FROM
    TABLE_A A, TABLE_B B

    where
    B.ROW_ID2 = A.ROW_ID1
    and A.TYPE <> 'Department'
    and A.STATUS = 'Closed'
    AND A.RESULT = 'Pass'
    and to_date(B.START_DT,'DD-MON-YY')  between next_day(to_date(:P240_date,'DD-MON-YY')-7, 'Sunday')
    and next_day(to_date(:P240_date,'DD-MON-YY'), 'Saturday')
    and B.OWNER = :P240_OWNER
    and (B.CATEGORY like 'AB%' or B.CATEGORY like 'DS%')
    +)+
    GROUP BY TYPE
    +) closed ,+
    +(+
    SELECT
    MAX(type) type,
    MAX(SUM(CASE WHEN DAY = 'Sunday' THEN 1 ELSE 0 END )) sunday_tot,
    MAX(SUM(CASE WHEN DAY = 'Monday' THEN 1 ELSE 0 END)) monday_tot,
    MAX(SUM(CASE WHEN DAY = 'Tuesday' THEN 1 ELSE 0 END)) tuesday_tot,
    MAX(SUM(CASE WHEN DAY = 'Wednesday' THEN 1 ELSE 0 END)) wednesday_tot,
    MAX(SUM(CASE WHEN DAY = 'Thursday' THEN 1 ELSE 0 END)) thursday_tot,
    MAX(SUM(CASE WHEN DAY = 'Friday' THEN 1 ELSE 0 END)) friday_tot,
    MAX(SUM(CASE WHEN DAY = 'Saturday' THEN 1 ELSE 0 END)) saturday_tot,
    MAX(sum(completed_cnt)) CNT
    FROM
    +(+
    SELECT
    A.NUM NUM,
    B.OWNER OWNER,
    A.STATUS,
    +'A' type,+
    --+                   
    case when to_date(B.START_DT,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-7, 'Sunday')  then 'Sunday'
    when to_date(B.START_DT,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-6, 'Monday')  then 'Monday'
    when to_date(B.START_DT,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-5, 'Tuesday')  then 'Tuesday'
    when to_date(B.START_DT,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-4, 'Wednesday') then 'Wednesday'
    when to_date(B.START_DT,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-3, 'Thursday') then 'Thursday'
    when to_date(B.START_DT,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-2, 'Friday') then 'Friday'
    when to_date(B.START_DT,'DD-MON-YY') = next_day(to_date(:P240_date,'DD-MON-YY')-1, 'Saturday') then 'Saturday'
    end Day,
    +1 completed_cnt,+
    B.START_DT,
    row_number() OVER ( partition by A.num order by rownum) rn
    FROM
    TABLE_A A, TABLE_B B
    where
    B.ROW_ID2 = A.ROW_ID1
    and A.TYPE <> 'Department'
    and A.STATUS = 'Closed'
    -- AND A.RESULT = 'Pass'
    and to_date(B.START_DT,'DD-MON-YY')  between next_day(to_date(:P240_date,'DD-MON-YY')-7, 'Sunday')
    and next_day(to_date(:P240_date,'DD-MON-YY'), 'Saturday')
    and B.OWNER = :P240_OWNER
    and (B.CATEGORY like 'AB%' or B.CATEGORY like 'DS%')
    +)+
    GROUP BY TYPE
    +) completed+

    WHERE closed.type = completed.type

    Edited by: Daniel on May 2, 2012 11:19 AM
  • 10. Re: SQL too big for region source
    752894 Newbie
    Currently Being Moderated
    Hi,

    I broke the SQL into 5 different classic reports and its working fine now.
    But its taking little over 5min for the report to run. To overcome this, I am planning to schedule a job which will insert the values into a temp_table and I will use the temp table to generate my report.
    But
    - I have the items used multiple times in my sql (please refer to the above post)
    - if i just insert the data into the temp_table and do the calculations/ filtering part later, i should still not be able to fit the whole SQL in one single report.

    Please throw some suggestions so that I have still have my whole SQL in one single report and improve the performance.


    Thank you all in advance,
    Daniel
  • 11. Re: SQL too big for region source
    VC Guru
    Currently Being Moderated
    You can create a database view using the page items referenced as V('P240_OWNER')

    http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/bldapp_item.htm#HTMDB25335

    Let me know if this does not work?

    Thanks
  • 12. Re: SQL too big for region source
    752894 Newbie
    Currently Being Moderated
    Hi VC,

    Thanks for your suggestion. But I didn't get how to pass the parameters into the view since my report populates based on the input values.

    Anyways, I created a scheduled job which inserts all the data into a staging table and I am pulling the data from it.
    Now the performance is much faster.
    I might have to work and figure a solution for this kind of issues when I get chance and will post it.

    Thank you,
    Daniel
  • 13. Re: SQL too big for region source
    TexasApexDeveloper Guru
    Currently Being Moderated
    Daniel,
    Here is a small example to help with building a pipeline based report:

    Given you have a table to report off of called states, its structure looks like this:

    State_Abbreb : VARCHAR2(2)
    State_Name : VARCHAR2(30)

    I built a package to house the code to produce a pipelined based report like this:
    create or replace PACKAGE BENTRACK_REPORTS
        AS
    
    /***************************
    || Name : BENTRACK_REPORTS
    || Author : Tony Miller
    || Date : 3/10/2012
    || Purpose : Package specification for BENTRACK Application Reports Package.
    ||          
    || Change History :
    ||     
    ***************************/
    
        TYPE  States_Rec_Type IS TABLE OF STATES%ROWTYPE;
    
        FUNCTION get_State_Data RETURN SYS_REFCURSOR;
    
        FUNCTION get_State_Rpt(p_cursor SYS_REFCURSOR) RETURN BENTRACK_REPORTS.States_Rec_Type PIPELINED;
    
    END;
    
    
    create or replace package body BENTRACK_REPORTS is
    
        
    /***************************
    || Name : get_State_Data
    || Author : Tony Miller
    || Date : 3/10/2012
    || Purpose : Function that creates REF_CURSOR of State data.
    ||         : REF_CURSOR is consumed by get_State_Rpt used in
    ||         : APEX Interactive Report via a SELECT with TABLE() wrapped around 
    ||         : function call in select.     
    ||    
    || Change History :
    ||     
    ***************************/
        
    FUNCTION get_State_Data RETURN SYS_REFCURSOR
        IS
        l_ret_rc SYS_REFCURSOR;
    BEGIN
        OPEN l_ret_rc FOR
          SELECT * FROM STATES 
          ORDER BY 1;
        RETURN l_ret_rc;
    END;
    
    
    FUNCTION get_State_Rpt(p_cursor SYS_REFCURSOR) RETURN BENTRACK_REPORTS.States_Rec_Type PIPELINED
        IS
        
    /***************************
    || Name : get_State_Rpt
    || Author : Tony Miller
    || Parameters: p_cursor : REF_CURSOR of States data.    
    || Date : 3/10/2012
    || Purpose : Function that consumes REF_CURSOR of State data and returns
    ||         : rows via pipelined functionality to APEX Interactive Report via a 
    ||         : SELECT with TABLE() wrapped around function call in select.     
    ||    
    || Change History :
    ||     
    ***************************/
        
        
        l_row STATES%ROWTYPE;
        
      BEGIN
        LOOP
          FETCH p_cursor INTO l_row.ST_CODE,
                              l_row.ST_NAME;        
            EXIT WHEN p_cursor%NOTFOUND;
            PIPE ROW( l_row);
        END LOOP;
        RETURN;
      END;
    
    END;
    
    Now in your report for the source of your report you use:
    
    SELECT * 
    FROM TABLE(BENTRACK_REPORTS.get_State_Rpt(BENTRACK_REPORTS.get_State_Data)); 
    This will act JUST like a regular select and also allow you to have your ginormous query built in your package and allow it to compile properly.

    Let me know if you have any questions..



    Tony Miller
    Dallas, TX
  • 14. Re: SQL too big for region source
    752894 Newbie
    Currently Being Moderated
    Hi Tony,

    Thanks for your solution. Your method worked awesome. Now I could show all my data in a single report.

    I apologize for getting back late, since I was tied up with other work. Tried your advice this week and it worked great.

    I am stuck with another issue now, it would be great if your could throw some light on this too.

    In the above query, I have my column names as SUNDAY, MONDAY.....SATURDAY. I have a Date_Picker above the region and my requirement needs the date to be concatenated to the column_name. For example, if I select 31-MAY-2012 the column names have to dynamically change as below.

    SUNDAY 27-MAR-2012, MONDAY 28-MAR-2012, ............... SATURDAY 02-JUN-2012

    I tried concatenating to the alias name writing another SELECT statement but released its incorrect. Please let me know if have any suggestion on this or is it feasible or not.


    Thank you once again,
    Daniel

    P.S. Please let me know if I must open a separate thread for this new issue.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points