|
|
|
Junior Member
 
Group: Forum Members
Last Login: 4/22/2007 2:58:00 PM
Posts: 18,
Visits: 1
|
|
Needs extract only mondays in 2007.
how can i achevie using Teradata SQL
Thanks
Chinna
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 12:53:07 PM
Posts: 118,
Visits: 60
|
|
Chinna,
Your question is very vague.
Do you have a table which has a date column and do you want to extract Mondays from those values
or
in general from calendar view?
Yet I guess this select will help you achieve if you change it according to your needs.
The following SQL extracts all Mondays in the date range mentioned in the where clause.
select * from sys_calendar.calendar where day_of_week=2 and calendar_date between date -77 and date
order by 1;
Also check this URL when you have time.
http://www.teradataforum.com/attachments/a040409b.doc
Hope this serves your question.
Regards
Ramakrishna_Vedantam
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 6/27/2008 7:19:28 AM
Posts: 42,
Visits: 54
|
|
|
Do you think multi-posting is the best way to get an answer ?
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: Yesterday @ 6:22:18 AM
Posts: 25,
Visits: 35
|
|
The syntax would be like this:
Sel*
from tablename
where Tablename_date_column IN(
select Calendar_Date from sys_calendar.calendar where day_of_week=2 and extract(year from calendar_date) = 2007 order by 1)
;
Remember that for Teradata, Sunday is the first day of the week.
Regards,
Andrew C. Livingston
Teradata Certified Professional and Trainer
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 1/28/2008 8:40:36 AM
Posts: 5,
Visits: 10
|
|
Hi Mr. Livingston,
I tried your query, and got an error that order by should not be used in the sub query. Can you give any other alternative.
Regards,
Ravi
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 12:24:48 AM
Posts: 425,
Visits: 389
|
|
|
The "order by 1" clause is not required, he must have accidentally put it there ...... remove it and the query will work fine.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 12:56:30 AM
Posts: 76,
Visits: 84
|
|
Sub queries can not have order by clause.(I have read it too)
But can anyone give the reason for the same?
Regards,
Sakthi Do your duty Dont expect the reward, God will give the benefit for you
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 12:24:48 AM
Posts: 425,
Visits: 389
|
|
That's because it's pointless to have an order by clause in a subquery.
For example
if you write
SEL A FROM TAB1 WHERE A IN (SELECT B FROM TAB2 WHERE B IS NOT NULL)
your are saying you need all the "A"s from tab1 which is also there in tab2 (as column B).
now if you write it as
SEL A FROM TAB1 WHERE A IN (SELECT B FROM TAB2 WHERE B IS NOT NULL ORDER BY 1)
This does not make any difference to the query's output or semantic meaning, because in set theory { a, b, c, d } = { b, c, d, a } = { c, d, a, b } etc ..... ie the order of elements doesn't matter... the IN sql operator is a set operator, so the order of elements in the subquery output is irrelevant for the operator. so db doesn't want to do an extra effort of doing an ordering, which doesn't alter the semantics of the query. This is the same reasoning why the order by operator is not allowed at other places in subqueries, these are all set operations in one form or the other.
|
|
|
|