I have created a Date dimension table using sys_calendar.calendar table, but when i use a select statement on my Date_Dim table, the order of the rows is very eratic, is there a way i can change the way it returns the rows..say i want to have it ordered by the date field, i wanted to do it at the table level.
I am not interested in using the Order by along with the Selct statement. When i use the Select * from Date_dim, i wanted to see the retrieved rows default sorted by the date.
One of the tenets in basic relational theory is that order of the columns and rows in a table is irrelevant. If your results come out in the order you'd like them to it's probably dumb luck.
A select * query is be default an all amps query, huge data skew notwithstanding, and the order of the rows is again irrelevant, you've asked the db to give you all the rows without any qualifications on how their ordered.
Without the order by clause, there's no guarantee what order the rows will be presented.
FWIW, the order by clause doesn't go to work until after the resultset is complete.
sounds like OP expects an oracle IOT-way in TD, but you might need to know first the fundamental HUGE difference between share-nothing MPP design in TD and share-everything design in oracle. i'd like to say - enjoy the "order by" along with "select" in TD. TD is not oracle in almost any way.
Thats true, and we have many discussions on the forum relating to comparison between Teradata and Oracle/other RDBMS.
But you can surely get all the DWH needs fulfilled by simple or complex solutions.
Hello Adeel, i am trying to create a view from your suggestion with SQL below
CREATE VIEW VW_TEST
(calendar_date, year_of_calendar, quarter_of_year)
ORDER BY calendar_date;
But its coming as error, ORDERBY is not allowed in Subqueries, can you please hlep.
I ahve tried putting the ORDERBY before and after the From statement, but its not working.
AFAIK, You can not use Order by inside a view, You can only use Order by within a view with last select of a set operation e.g Union
Create view Test
Order by 1,2,3...
My bad Reddy .... yes you can't have an order by in a view.
Having it in a MACRO is possible, but i believe it wont be very useful in your scenario as you cant use a MACRO in SELECT.
ORDER BY within a view is not allowed.
There's a workaround, but it's definitely not recommended: Add TOP.
REPLACE VIEW xxx AS SELECT TOP 100 PERCENT * FROM tab ORDER BY col;
But there's still no guarantee that a SELECT * FROM xxx will return a sorted result.
The main question is: why do you think you need to do this?
What's so complicated in adding ORDER BY?
Btw, in MS SQL Server people used to do the TOP 100 PERCENT to get sorted output and when the optiizer was enhanced to eliminate that unnneccessary sort they complained that their application fails. Later they started using TOP 99.99999999999 PERCENT instead :-)
we have been developing reports , we have a "Select query with multiple joins and columns , retrieved from a report , but when we run the same query in teradata the sort differs with the original report sort though we use the same "order by" clause that we use in the report... This has been a never ending issue with "Order By". I just also want to make sure
1.If casting of dates from its original format to a varchar date would be a reason for this sort variation...
2.one more observation is if the columns contributing to the "Order By" clause has same data then the data is being sorted on the some other column , on what basis is this column chosen.
I have ensured it meets all expectations for "order by " defined here
This sort issue should be resolved asap ,Please share your thoughts as this is a very wierd issue we have been facing...
Any help is greatly appreciated.
Thanks in Advance!
I never encountered problems with sort orderm but without showing both "order by" it's hard to tell...
Q1: Of course casting a date to string might change the order if it's not based on yyyymmdd.
Q2: if I understand correctly you do something like this:
CAST(datecol AS CHAR) AS datecol
ORDER BY datecol
In this case you order by the column datecol, not the alias.
Now if this is created as a view and you run
FROM my view
ORDER BY datecol
you order by the alias datecol :-)