Reports sorting

From: Jason King <jhking_at_airmail.net>
Date: Mon, 19 Jul 1999 01:11:51 -0500
Message-ID: <8B53898A57A42D7E.59BFF8FE3B32E871.54A9F81EC2AFDA50_at_lp.airnews.net>

Oracle Reports mangles my select based on my groups If my sql is SELECT inv_num , item , category , sub_category , etc

                     FROM invoice
                    WHERE inv_num = :P_INV_NUM
                   ORDER BY item

and I group on category, then sub-category Reports changes my ORDER BY clause like so:

    ORDER BY category , sub_category , item

The data looks like
INV_NUM ITEM CATEGORY SUB_CATEGORY

1                    1     Cat1                Sub1
1                    2     Cat1                Sub1
1                    3     Cat1                Sub2
1                    4     Cat1                Sub2
1                    5     Cat1                Sub2
1                    6     A Cat2            Sub1

There should be SUB_CATEGORY breaks after row 2 and 5 and CATEGORY break

after row 5.
Because I need sub-totals and white-space I need groups at CATEGORY and SUB_CATEGORY

1                    6     A Cat2            Sub1 <=
                                                             A Cat2
sorts before Cat1
1                    1     Cat1                Sub1
1                    2     Cat1                Sub1
1                    3     Cat1                Sub2
1                    4     Cat1                Sub2
1                    5     Cat1                Sub2

My work-around is to create 2 views 1 as SELECT inv_num , category , min( item ) FROM invoice GROUP BY inv_num , category
and another as
SELECT inv_num , category , sub_category , min( item ) FROM invoice GROUP BY inv_num , category , subcategory and then join the base table to the two views. I had previously tried 3 separate related queries but performance was atrocious. Performance with the 2 views isn't bad ( in test ) but it galls me to have to do a 3 way self join to get the rows to appear in the order I want.

Is there some way to
1) change the SQL: back, like in a pre-parameter or post-parameter trigger. or
2) Get the control-breaks without reports jacking with the ORDER BY clause. Received on Mon Jul 19 1999 - 08:11:51 CEST