Stored procedure conversion oracle to TD

Analytics
Fan

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

3 REPLIES
Senior Apprentice

Re: Stored procedure conversion oracle to TD

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?

Fan

Re: Stored procedure conversion oracle to TD

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

Senior Apprentice

Re: Stored procedure conversion oracle to TD

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

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