All Forums Analytics
Natu 3 posts Joined 12/11
19 Apr 2016
Stored procedure conversion oracle to TD

Hi All,
I am trying to convert the stored procedure from oracle to TD, below is the cursor used in Stored procedure 
 

CURSOR pnr_od_cur IS

      SELECT s.pnr_sk,

             REGEXP_SUBSTR(true_od, '[^;]+', 1, level) true_od_split

        FROM cdw_pnr_od_const_wrk s, cdw_od_pnr o

       where processed = 'Y'

         and s.pnr_sk = o.pnr_sk

      CONNECT BY LEVEL <=

                 LENGTH(true_od) - LENGTH(REPLACE(true_od, ';')) + 1

             AND s.pnr_sk = PRIOR s.pnr_sk

             AND PRIOR dbms_random.value IS NOT NULL;

 

Please help me in converting this cursor

dnoeth 4628 posts Joined 11/04
19 Apr 2016

Oracle's CONNECT BY is proprietary syntax, usually rewritten using WITH RECURSIVE, but I don't know if you can do that in a cursor.
Looks like splitting data into multiple rows, there's a high probability that this can be done using REGEXP_SPLIT_TO_TABLE. But what's the meaning of last conditon based on dbms_random?
 
Can you add some actual data and what's returned?

Dieter

Natu 3 posts Joined 12/11
20 Apr 2016

Hi Dieter,
Its is the stored procedure which been already exisiting in Oracle database, below is the cursor declaration in Oracle stored procedure
    CURSOR pnr_od_cur IS

      SELECT s.pnr_sk,

             REGEXP_SUBSTR(true_od, '[^;]+', 1, level) true_od_split

        FROM cdw_pnr_od_const_wrk s, cdw_od_pnr o

       where processed = 'Y'

         and s.pnr_sk = o.pnr_sk

      CONNECT BY LEVEL <=

                 LENGTH(true_od) - LENGTH(REPLACE(true_od, ';')) + 1

             AND s.pnr_sk = PRIOR s.pnr_sk

             AND PRIOR dbms_random.value IS NOT NULL;

 

i am just trying the same in teradata, we are converting the oracle stored procedure into teradata stored procedure...

dnoeth 4628 posts Joined 11/04
20 Apr 2016

You posted the code already, but what is this code doing?

Can you add some actual data and what's returned?

Dieter

You must sign in to leave a comment.