Archives of the TeradataForum

Message Posted: Thu, 11 Dec 2008 @ 13:38:36 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Exception handling for stored procedures and functions

From:		Dieter Noeth

Glenn David McCall wrote:

I don't know Oracle SP's at all, but I'm guessing this procedure takes a date and time (as an integer hhmmss format) and if the hours is < 5 extracts yesterdays day otherwise extracts today's day (e.g. 11-Dec-2008 day is 11).

Almost, but 'D' is the day of week.

It just looks complicated, but in fact it's quite simple :-)

What is unclear is how does "dual" fit into this?

It's a dummy table, because Oracle always needs a FROM clause.

This could probably be achieved via something like this.

          > > 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.

Also, how does the exception fire? If there is no data in dual?

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.

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference