This content has been marked as final. Show 10 replies
lpad(wp.work_package_id,5,'0')||' - '||wp.name wp_name,
wp.project_id = p.project_id
wp.RELEASE_ID = :P19_RELEASE_ID
wp.DELETE_FLAG = 'N'
wp.WORK_PACKAGE_ID = pt.WORK_PACKAGE_ID
pt.DELETE_FLAG = 'N'
p.project_id = DECODE(:P19_PROJECT_ID,-1,p.project_id,:P19_PROJECT_ID)
:P19_SHOW_CLOSED_ITEMS = 'Y' or p.project_status != 'C'
order by 1,3,5
The colum alias I changes was for column 4 (wp_name).
I also get the same problem with:
"DELETE_FLAG" = DECODE(:P68_SHOW_DELETED,'N','N',"DELETE_FLAG")
"PARENT_WORK_PACKAGE_ID" = :P68_WORK_PACKAGE_ID
Both these queries used to work OK, and the 'fix' in my original post has fixed the first issue (proving that the SQL is OK). I have not fixed the second one yet - hoping for a beter method.
I think this is want you want (sorry it's not easy to read):
wp.suite_id - Database Column NUMBER - Report Column Based on LOV
wp.project_id - Database Column NUMBER - Report Column Based on LOV
wp.application_order wp_order - Database Column NUMBER - Report Column Standard
lpad(wp.work_package_id,5,'0')||' - '||wp.name wp_name - Database Column NUMBER/VARCHAR2 - Report Column Standard
pt.application_order pt_order - Database Column NUMBER - Report Column Standard
pt.name pt_name - Database Column VARCHAR2 - Report Column Standard
substr(pt.description,1,30) description - Database Column VARCHAR2 - Report Column Standard
pt.application_tier - Database Column VARCHAR2 - Report Column Based on LOV
pt.application_method - Database Column VARCHAR2 - Report Column Based on LOV
pt.TECH_LEAD_ID - Database Column NUMBER - Report Column Based on LOV
This report has no link columns and no derived columns.
I don't know how your procedure happens to fix the problem.
You might want to look at the HTML page source of the generated report and see if you're getting close to the 32K limit per row (sum of all bytes sent to browser for a row). If the LOV queries return too many rows, they could produce that error. In fact that's more likely and you may need to shrink them.
I can't see anything that would take me over the 32K row limit:
wp.application_order wp_order 5,
lpad(wp.work_package_id,5,'0')||' - '||wp.name wp_name 35,
pt.application_order pt_order 5,
pt.name pt_name 30,
substr(pt.description,1,30) description 30,
Total Length : 124 Bytes
# of Rows : Max 200
Total Size : 24800
wp.suite_id - LOV Return, Display / Rows : 3, 30, 37
wp.project_id - LOV Return, Display / Rows : 3, 30, 81
pt.application_tier - LOV Return, Display / Rows : 1, 30, 4
pt.application_method - LOV Return, Display / Rows : 1, 30, 6
pt.TECH_LEAD_ID - LOV Return, Display / Rows : 1, 30, 163
Using the second query I removed all the 'LOV' columns from the report (taking tham back tostandard columns) and the report ran OK.
The offending LOV seems to be based on my WORK_PACKAGE_ID which is a growing list (but still fairly small in the scheme of things).
I have changed the base query to do the lookups directly and left all columns as 'Standard' columns and everything seems OK.
I think for future apps I may consider having a 'reporting view' for each table that does all the joins for me - I should then be able to build reports as big as I want.
Many thanks for your thoughts and suggestions.