on 11-14-2011 6:39 PM
I have a derived table in the universe that has fiscal year and fiscal month and I need to sort it and i used the order by
at the end of the query after by group by. The sql works when I execute it against the database through a query tool, but it
does not work in the derived table. I get an error - teradata database syntax error : that order by is not allowed in subqueries: state 42000
There are about 8 fields in my sql and I need to order by dataset by the first 2 fields only. Is there an alternate solution for this?
fiscal year starts in Jun and ends
in July.
Expected sort order - sample values:
Fiscal year Fiscal Month
2011 5
2011 5
2011 6
2011 7
2011 8
2011 9
2011 10
2011 11
2011 12
2012 1
2012 2
2012 3
2012 4
2012 5
2012 5
You need to not worry about this for a couple of reasons.
As the error message advised, this is not possible. Your RDBMS treats the derived table as a subquery, which is all it is.
A derived table will have a different sort order to the BusinessObjects report anyway.
It is the solution that I least like - the DBA doesn't know about it so won't tune the indexes for it and there will be no impact analysis when new database development is done. The performance of a view at least passes the visibility and responsibility on to the DBA.
So, no, you cannot do it and there is no point to doing it anyway.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
One thing that I have noticed is if I have a top 13 in my select clause , then the order by works. But I cannot hardocde 13, since it may be more than 13 rows. Is there a workaround for this issue with derived tables and order by.
select top13 , column a....
from (
) subtable
group by 1,2
order by 1,2
works. But the issue is I cannot hardcode 13, there could be more rows than that.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.