Undesirable effect: Reports alters ORDER BY clause
Date: Thu, 06 May 1999 00:29:53 GMT
Message-ID: <7gqnpt$jud$1_at_nnrp1.deja.com>
I am writing in Reports 2.5 (NT 4.0), trying to create a generic report in 3 or 4 flavors. It is controlled by passed parameter, PARM_SELECT which contains the entire query. The query is prepared in Forms at button-press time just prior to RUN_PRODUCT.
Because of my break groups, Reports is overriding my query by inserting its own ORDER BY clause and adding my ORDER BY fields to the end:
select a,b,c.... from table order by b becomes
select a,b,c...from table order by 1 asc, 2 asc......,15 asc, b
This means that no matter how I contruct the order by, the report result will always return in the same order (1 ASC).
My data model looks like this: Q_1 -> G_1 (many fields: a,b,c,d,e,...) -> G_2 (child relationship of three fields, x,y,z) Because Oracle recommends less queries for better performance, this data model is built on a single query which returns the G_1 parent and G_2 children in one row, through a join.
Report output is
a, b, c,.... spread out and customized in a 2 inch block
x,y,z children appear in smaller block
within parent block, vertically elastic.
I really only need to break on one field in G_1, the others are display only
and not involved in the break. Nevertheless, REPORTS add ALL of them to the
ORDER BY. Needless to say, this is a potential performance hit since Reports
is adding 15 fields to the Order By.
Does anyone have experience with Passing Queries through parameter blocks, and any advice on organizing the data model to cause Reports to put less fields in the Order By clause?
If it turns out I have to create 3 or 4 separate flavors of report it will be a real FUDGE! :-( [can anyone relate?]
Thanks Much
Robert Proffitt
Beckman/Coulter
Brea, CA
RTProffitt_at_Beckman.com
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu May 06 1999 - 02:29:53 CEST