cancel
Showing results for 
Search instead for 
Did you mean: 

UNiverse designer sort order

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

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.

Former Member
0 Kudos

I have sort order (order by clause ) in another derived table and it works there , but does not work in the a new derived table. the sql runs from client tool that connects directly to the database.

Former Member
0 Kudos

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.

Former Member
0 Kudos

I resolved this by just using top 100 by default. I know for sure my data is not going to be larger than that. This question can be closed. Thanks.