Archives of the TeradataForum

Message Posted: Wed, 16 Feb 2005 @ 18:02:06 GMT

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

Hi all,

I am trying to change this oracle code into teradata. I am assuming it is in pl/sql. I am thinking of changing it using stored procedures but not sure if that is the right way.

I will really appreciate if one can suggest a better way. An example will be also very helpful.

Thanks in advance !

     ================================
     DECLARE

     last_date DATE;
     mean NUMBER;
     RESULT NUMBER;
     rec NUMBER;


     BEGIN

     /********* determining the latest weekday in the table********/

     select max(a.Bus_date) into last_date from payments a;


     /********* counting records on the latest weekday *********/

     select count(*) into rec from payments a where a.BUS_DATE = last_date;

     /********* counting sum of all records for the same weekday in last 8 months ***/

     select avg(count(*)) into mean from payments a where
     a.BUS_DATE > (last_date - 240) and a.BUS_DATE < last_date and
     to_char(a.BUS_DATE, 'DAY') = to_char(last_date,'DAY')
     group by a.BUS_DATE, to_char(a.BUS_DATE, 'DAY');


     /***** calculating mean and variance *********/

     RESULT := ROUND(((rec - mean)*100/mean),2);

     /********** inserting in metrics table *******/
     INSERT INTO TDI_DAILY VALUES (trunc(sysdate), 'PAYNUM', RESULT, NULL,
     'TESTED ' || last_date || ' - ' || to_char(last_date,'DAY'));

     END;
     /
     commit
     /

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