order by doesnt work

[meann]

hello to all!
i have one data model which compose of 2 groups, let us say group is name as
G1 and the other group is G2.
G1 has a,b,c,d,e fields and G2 has f,g,h,i,j fields. In other words, fileds f,g,h,i,j are group by a,b,c,d,e.

I used the user parameter to pass the order by clause because I have many possible order by condition.
When I pass the parameter "order by g desc,a asc,b asc" the report output was ordered by a,b, then g.
why? is it because in data model a and b was at the top of g. is there a way to fix this
to be able to meet my order by condition. PLEAS HELP ME!

my data model looks like this:
Q1
|
___|_____
| a |
| b |
G1-> | d |
| c |
| e |
|_______|
|
____|___
| f |
| g |
G2-> | h |
| i |
| j |
|_______|

 

[SQLBill]

My guess (I'm looking into it more) is that with order by the value 'asc' has precedence over 'desc'. So all 'asc' order by commands are executed first, then the 'desc' order by commands. Quick check to confirm this: change the command to: order by g asc, a desc, b asc. If asc is done first, then the order should execute as g, b, then a.

 

[subzero82]

I am pretty sure that asc and desc are evaluted equally and should be evaluated as they appear in the order by clause. If you run this query in SQL Plus you should see the results as you wanted them. I believe that the issue lies with the grouping. Man, thats a really good quetion though. I have had similar issues with grouping and order by and have changed layouts before due to this. I am going to try it right now and I will post my results for you. In the meantime you might want to check out

http://www.revealnet.om

and join their forum as well. They have some excelent info that might help in the meantime.

 

[juliecb]

I think you're right, the problem is with the data model. I had a similar problem with a standard order by. When I dragged the fields around the problem was solved. I don't know if you can get around this if you pass the order by via the user parameter but I'll be watching this post to see if someone figures it out.

 

[freedomUSA]

This may sound stupid at this point, but I have let it slip by a few times wreaking havoc, but, you did make sure you took away all the default break order settings from your fields right?

 

[Guest_imported]

Since you are making a break report, all the fields having the break order set in the Group G1 will be ordered first irrespective of the way you have written your order by. This is a feature of reports as I know. If you want it to act your report to run differently, you should change your data model design of the report.

 

[mutinda1967]

Can someone help me define a order by clause for my report. I found this thread in the forum and it has almost the same problem I have.

I defined the parameter &sort then tried the following in my report but it didn't work

select, lastname, City
From prospect
ORDER BY &SORT; -- where &sort would be Lastname,city

when I run this I get error ORA-00936

what am I missing here?