Re: ORDER BY clause within VIEW

I always thought ORDER BY is not allowed. However, looking at the syntax diagram in Chapter 3: SQL Data Definition Language Statement SyntaxCREATE VIEW REPLACE VIEW in SQL/data Dictionary quick reference, I was surprised that it says otherwise.

However, when we use ORDER BY , we get 3706 ERROR which says "SYNTAX ERROR : ORDER BY IS NOT ALLOWED IN SUBQUERIES". From this, looks like the select statement in View is treated as a subquery and that's why we get this error.

Re: ORDER BY clause within VIEW

Order by is not used in view clause(DML),I thought view is just a sql, and when you read data from view, if there is oder by , this will cause the data to redistribute, in fact , if you want the data to be ordered, you may use select * from view order by ...so no need to use order by in view clause(DML)

Re: ORDER BY clause within VIEW

We can't use ORDER BY clause in View defintion and in subquery.

As both are not the final result set.

Teradata is designed to perform in most optimized path.

If there is an option to put oder by in any of above cases, it means Teradata mean to order the intermediate result set. Which means results in SPOOL should be ordered. This will degrade the performance.

So in order to have maximum performance and best PE plan ORDER BY clause is not allowed in View definition and in Sub queries or any where which is NOT the final results.