Creating a recursive script to handle Oracle script

Database
Fan

Creating a recursive script to handle Oracle script

I am new to TD and have an Oracle script i need to convert to TD format. I am not understanding how to make these changes using recursive script procedures to make it happen. Any help with this sub-select query would be greatly appreciated. The result set is by Q1-YYYY, Q2-YYYY etc.. by site description. Thanks for the help.

Code sample:

(

SELECT 'Day' as Time_Period,

to_char( trunc( sysdate ) - level, 'mm/dd' ) as Period,

trunc( sysdate ) - level as start_dt,

trunc( sysdate ) - level + 86399/86400 as end_dt

from dual

connect by level <= 7

union all

-- RETURNS LAST 4 QUARTERS (BEFORE TODAY)

SELECT 'Quarter' as Time_Period,

'Q' || to_char( add_months( trunc( sysdate, 'Q' ), - ( level * 3 ) ), 'Q-YYYY' ) as Period,

add_months( trunc( sysdate, 'Q' ), - ( level * 3 ) ) as start_dt,

add_months( trunc( sysdate, 'Q' ), - ( ( level - 1 ) * 3 ) ) - 1 + 86399/86400 as end_dt

from dual

connect by level <= 4

union all

-- RETURNS LAST 6 MONTHS (BEFORE TODAY)

SELECT 'Month' as Time_Period,

to_char( add_months( trunc( sysdate, 'MM' ), - level ), 'MON-yy' ) as Period,

add_months( trunc( sysdate, 'MM' ), - ( level ) ) as start_dt,

add_months( trunc( sysdate, 'MM' ), - ( level - 1 ) ) - 1 + 86399/86400 as end_dt

from dual

connect by level <= 6

union all

Tags (1)
1 REPLY
Teradata Employee

Re: Creating a recursive script to handle Oracle script

Hi,

Instead of keeping it recursive, you should better change the logic to sequencial and then implement it .... it will have much better performance that way.

Regards, Adeel