Archives of the TeradataForum
Message Posted: Thu, 11 Dec 2008 @ 13:38:36 GMT
Glenn David McCall wrote:
Almost, but 'D' is the day of week.
It just looks complicated, but in fact it's quite simple :-)
It's a dummy table, because Oracle always needs a FROM clause.
> > select > > case > > when p_time / 10000 < 5 then > > extract (day from (p_Date - interval '1' day)) > > else extract (day from p_Date) > > end > > from dual;
A DATE in Oracle is a Timestamp, so it's a bit more complicated:
((CAST(p_Date AS DATE) - CASE WHEN p_time / 10000 < 5 THEN 1 ELSE 0 END) - DATE '0001-01-01') MOD 7 + 1
This is for ISO weeks.
There will be no exception. There's always a single row in dual and the optimizer knows about that. Btw, in old releases it was fun to insert another row into dual to see everything break, don't know if this is still possible ;-)
Of course it should be easy to rewrite that function as a Teradata UDF, i still hope for a future release to allow SQL-functions instead of C- functions.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|