Archives of the TeradataForum

Message Posted: Thu, 11 Dec 2008 @ 12:37:36 GMT

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

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

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

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;

Also, how does the exception fire? If there is no data in dual? If so this query will return an empty result set. If you must force a single null row, you could perform a right outer join to select 1

For example:

     select
          case
            when D.some_column_in_dual is null then null
            when p_time / 10000 < 5 then
                    extract (day from (p_Date - interval '1' day))
            else    extract (day from p_Date)
          end
     from dual D right outer join (select 1) as DT;

To make it more useful, you might write it as a macro. That way you can parameterise the p_time and p_date values when you exec the macro.

I haven't tested these queries; and as I said I don't know Oracle SP's, so I have no idea if the above does what you need, but hopefully it might give you some ideas. Sadly I don't know informatica either, but I assume it can run a regular query if it can run an SP.

As a general rule if you are doing row at a time processing and that processing isn't dependent upon the previous rows (i.e. each row is processed on its own merits), you can perform the operation as a set operation and it will run faster due to the parallel nature of Teradata. With the newer releases of Teradata and capabilities such as windowed functions, derived tables and so on, a lot of queries that do depend upon the previous rows can also be written as set operations and will also run faster than the equivalent serial processing.

Hope this helps

Glenn Mc

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