locked
need to convert PL/sql to sql RRS feed

  • Question

  • User351619809 posted

    Hi All,

    I need to convert an oracle stored proc to sql. Below are the stored procedures

    CREATE OR REPLACE PROCEDURE COLNG.GetCOLreport
    -- ( 
    -- p_date_from IN varchar2,
    --                                        p_date_to IN varchar2
    --                                        p_metrics OUT TYPES.cursorType
    --                                        )
      IS
        v_msg  varchar2(500);
        v_date_from date;
        v_date_to date;
        
         BEGIN
    
         v_date_from := to_date('01/01/2010','mm/dd/yyyy');
         v_date_to := to_date('11/01/2011','mm/dd/yyyy');
         
         execute immediate 'truncate table temp_COL_timeline';
    
               for each_day in (
                     with day_by  as
                          ( select v_date_from + rownum -1 as timeline
                                from raw_cascade_header 
                                where rownum <= 
                                       v_date_to - v_date_from+1
                          )
                     select db.*
                     from day_by db
                       ) loop
                    
    
                        insert into temp_COL_timeline
                                        (TIMELINE, UPC, INITIAL_DT, INITIAL_DTG, cascadeCOR_DT, report_NUM, cascadeNUM)
                              select each_day.timeline, a.UPC,a.initial_dt, a.initial_dtg, a.cascadecor_dt, a.cascadenum, a.cascadenum
                              from raw_cascade_header A
                              --VW_cascade_FOR_TIMELINE a
                              where (a.initial_dt < to_date(to_char(each_day.timeline, 'yyyymmdd')||'2359','yyyymmddhh24mi') and (a.cascadecor_dt is null or a.cascadecor_dt > to_date(to_char(each_day.timeline, 'yyyymmdd')||'2359','yyyymmddhh24mi'))
                                         OR
                                         (a.total_updates=1 and a.current_update_no in (101,102) and trunc(a.initial_dt) = trunc(each_day.timeline))
                                         OR
                                         (trunc(a.initial_dt)=trunc(a.cascadecor_dt) and a.initial_dt < to_date(to_char(each_day.timeline, 'yyyymmdd')||'2359','yyyymmddhh24mi') )
                                        
                                         )
                               and UPC in (select UPC from daily_ship)
    --                           and exists (select 1
    --                           from raw_cascade_version where UPC=a.UPC and initial_dtg=a.initial_dtg
    --                           and cascadenum=a.cascadenum and severity_cd in (3,4)) 
                               ;
                                                          
               
               end loop;
               
               for rec in (select* from temp_COL_timeline) loop
                 If  trunc(rec.initial_dt)=trunc(rec.cascadecor_dt) and trunc(rec.initial_dt)=rec.timeline Then
                      UPDATE temp_COL_timeline A
                       SET A.LAST_SEVERITY = 
                                        (SELECT max(SEVERITY_CD)
                                         FROM raw_cascade_version
                             WHERE  UPC=REC.UPC and
                                         initial_dtg=rec.initial_dtg and
                                         cascadenum=rec.cascadenum
                                AND (report_update_no=101 or
                                         report_update_no=102)
                                         )
                            WHERE a.UPC = rec.UPC  and a.initial_dtg=rec.initial_dtg
                               and a.cascadenum=rec.cascadenum
                              and a.timeline=rec.timeline;
                 ElsIf rec.cascadecor_dt is null or rec.cascadecor_dt > TO_DATE(TO_CHAR(rec.TIMELINE, 'YYYYMMDD')||'2359', 'YYYYMMDDHH24MI') Then
                 --  v_msg := 'cascadenum '||rec.report_num||' timeline '||rec.timeline;
                   
                  -- v_date := null;
    --           SELECT MAX(report_update_dt) into v_date
    --            FROM report_version
    --             WHERE report_id=rec.report_id
    --              AND report_update_dt <= TO_DATE(TO_CHAR(rec.TIMELINE, 'YYYYMMDD')||'2359', 'YYYYMMDDHH24MI');
                                 
                         UPDATE temp_COL_timeline  A
                         SET
                          A.LAST_MSG_DT = 
                                (SELECT MAX(msg_date) 
                                 FROM raw_cascade_version
                                 WHERE UPC=REC.UPC and
                                         initial_dtg=rec.initial_dtg and
                                         cascadenum=rec.cascadenum
                                 AND msg_date <= TO_DATE(TO_CHAR(rec.TIMELINE, 'YYYYMMDD')||'2359', 'YYYYMMDDHH24MI')
                              )
                              WHERE a.UPC = rec.UPC  and a.initial_dtg=rec.initial_dtg
                               and a.cascadenum=rec.cascadenum
                              and a.timeline=rec.timeline;
    -- v_msg := 'cascadenum '||rec.report_num||' timeline '||rec.timeline;
     
                       UPDATE temp_COL_timeline A
                       SET A.LAST_SEVERITY = 
                                        (SELECT MAX(SEVERITY_CD)
                                         FROM raw_cascade_version
                             WHERE   UPC=REC.UPC and
                                         initial_dtg=rec.initial_dtg and
                                         cascadenum=rec.cascadenum
                                AND msg_date= A.LAST_MSG_DT
                                         )
                                WHERE a.UPC = rec.UPC  and a.initial_dtg=rec.initial_dtg
                               and a.cascadenum=rec.cascadenum
                              and a.timeline=rec.timeline                   ;
                          --     v_msg := 'cascadenum '||rec.report_num||' timeline '||rec.timeline;
                  End If; -- if report was still open on that day             
               end loop;
                  commit;
                    INSERT INTO
                     TEST_COL_TIMELINE (TIMELINE,  UPC  ,  INITIAL_DTG,  cascadeNUM  ,  INITIAL_DT ,
                                                  cascadeCOR_DT,  report_NUM ,  LAST_MSG_DT,  LAST_SEVERITY)
                    (SELECT* FROM TEMP_COL_TIMELINE);
                    COMMIT;
                    
               --  open p_metrics for
              --   select* from temp_COL_timeline;
    --                select A.*, B.SHIP_TYPE, B.HOMEPORT_COAST
    --                 from temp_COL_timeline A
    --                JOIN                    daily_ship b on a.UPC=b.UPC
    --                WHERE LAST_SEVERITY IN (3,4)
    --                AND SHIP_TYPE='FFG'
    --                AND HOMEPORT_COAST='L'
    --                ORDER BY SHIP_TYPE ;
                  
                    
    --                SELECT A.TIMELINE, A.LAST_SEVERITY, COUNT(*)
    --                  FROM temp_COL_timeline A
    --                  join daily_ship b on a.UPC=b.UPC
    --                  WHERE   
    --                      b.HOMEPORT_COAST = 'L'
    --                           AND b.SHIP_TYPE = 'DDG'
    -- AND 
    --                         a.LAST_SEVERITY IN (3,4)
    --                          GROUP BY A.TIMELINE, A.LAST_SEVERITY
    --                            ORDER BY 1;
     
      EXCEPTION
         WHEN OTHERS THEN
             v_msg := substr(sqlerrm, 1, 500);
      END GetCOLreport;
    /
    
    
    

    I am creating raw_cascade_header and raw_cascade_version with the below procedures

     

    DROP TABLE COLNG.RAW_cascade_HEADER;
    
    CREATE TABLE COLNG.RAW_cascade_HEADER
    (
      SHIP_TYPE          VARCHAR2(10 BYTE),  HULL_NUM_ID        VARCHAR2(10 BYTE),
      UNIT_ID            VARCHAR2(12 BYTE),  UPC                VARCHAR2(5 BYTE),
      INITIAL_DTG        VARCHAR2(12 BYTE),  cascadeNUM             CHAR(7),
      CURRENT_UPDATE_NO  NUMBER(3),  cascadeCOR_DT          DATE,
      IS_OPEN            NUMBER(1),  IS_CANCELLED       NUMBER(1),
      INITIAL_DT         DATE,
      TOTAL_UPDATES NUMBER(5)
      
    );
            
            insert into raw_cascade_header
            (ship_type, hull_num_id, unit_id,
             initial_dt, cascadenum)
            select a.unit_type, a.hull_number, a.unit_type||' '|| a.hull_number,
             to_date(a.initial_date,'YYYY-MM-DD HH24:MI:SS') , 
            a.cascadenum
            from rawtrms_report_2 a
            group by a.unit_type, a.hull_number, a.unit_type||' '|| a.hull_number,
             to_date(a.initial_date,'YYYY-MM-DD HH24:MI:SS') , 
            a.cascadenum;
            
            COMMIT;
                    update raw_cascade_header
            set initial_dtg=to_char(initial_dt, 'yyyymmddhh24mi');
            
            update raw_cascade_header a
            set a.UPC = (select UPC from daily_ship where ship_type=a.ship_type and ship_nbr=a.hull_num_id);
            
            delete from raw_cascade_header where UPC is null;
    
    
            CREATE TABLE RAW_cascade_VERSION as
                    select a.unit_type, a.hull_number,
                        to_date(a.initial_date,'YYYY-MM-DD HH24:MI:SS') as initial_date , 
            a.cascadenum, to_date(a.msg_date,'YYYY-MM-DD HH24:MI:SS') as msg_date,
            a.cascadecat , a.updatenum
            from rawtrms_report_2 a
            group by 
                             a.unit_type, a.hull_number,
                        to_date(a.initial_date,'YYYY-MM-DD HH24:MI:SS')  , 
            a.cascadenum, to_date(a.msg_date,'YYYY-MM-DD HH24:MI:SS') ,
            a.cascadecat   , a.updatenum
            ;
    ALTER TABLE RAW_cascade_VERSION ADD
    (INITIAL_DTG VARCHAR2(12),
    UPC VARCHAR2(5)
    );
    
    UPDATE RAW_cascade_VERSION SET
    (INITIAL_DTG)=TO_CHAR(INITIAL_DATE, 'YYYYMMDDHH24MI');
    
            update raw_cascade_version a
            set a.UPC = (select UPC from daily_ship where ship_type=a.unit_type and ship_nbr=a.hull_number);
            
            
       delete from raw_cascade_version where UPC is null;
       commit;
       
       delete from raw_cascade_header where initial_dt < '01-jan-2010';
       delete from raw_cascade_version where initial_date < '01-jan-2010';
       
       alter table raw_cascade_version add
       (report_update_no number(3));
       
       update raw_cascade_version
       set report_update_no=
        (cascadeE WHEN UPDATENUM= 'CA' THEN 102 
                                            WHEN UPDATENUM='CO' THEN 101
                                            ELSE TO_NUMBER(UPDATENUM)
                                            END);
     alter table raw_cascade_version add
       (severity_cd number(1));
       
       -- check for invalid severity before running this update
          update raw_cascade_version
       SET SEVERITY_CD =TO_NUMBER(cascadeCAT)
    WHERE cascadeCAT IN('1','2','3','4');
    COMMIT;
    
    
    CREATE INDEX COLNG.PK_RAW_cascade_HEAD ON COLNG.RAW_cascade_HEADER
    (UPC, INITIAL_DTG, cascadeNUM)
    LOGGING
    NOPARALLEL;
    
    
    CREATE INDEX COLNG.PK_RAW_cascade_VERSION ON COLNG.RAW_cascade_VERSION
    (UPC, INITIAL_DTG, cascadeNUM, report_UPDATE_NO)
    LOGGING
    NOPARALLEL;
    
      UPDATE RAW_cascade_HEADER a
              set a.current_update_no =
                   (select max(report_UPDATE_NO)
                     from RAW_cascade_VERSION
                     where UPC = a.UPC and initial_dtg = a.initial_dtg and cascadenum = a.cascadenum)
             ;
    COMMIT;
              
            UPDATE RAW_cascade_HEADER a
              set a.cascadecor_dt =
                   (select MSG_DATE
                     from RAW_cascade_VERSION
                     where UPC = a.UPC and initial_dtg = a.initial_dtg and cascadenum = a.cascadenum and report_UPDATE_NO=101
                    )
              ;
    
            UPDATE RAW_cascade_HEADER a
              set a.cascadecor_dt =
                   (select MSG_DATE
                     from RAW_cascade_VERSION
                     where UPC = a.UPC and initial_dtg = a.initial_dtg and cascadenum = a.cascadenum and report_UPDATE_NO=102
                    )
             WHERE A.cascadeCOR_DT IS NULL ;
    
                   UPDATE RAW_cascade_HEADER a
              set  a.is_open =0
              where 
               exists
                   (select 1
                     from RAW_cascade_VERSION
                     where UPC = a.UPC and initial_dtg = a.initial_dtg and cascadenum = a.cascadenum and
                               report_UPDATE_NO IN (101,102)) ;    
    
       update raw_cascade_header a
       set a.total_updates=(select count(*) from raw_cascade_version
                where UPC=A.UPC AND INITIAL_DTG=A.INITIAL_DTG AND cascadeNUM=A.cascadeNUM);
                COMMIT;
    
    I am 

     

     

     

    Thursday, October 27, 2011 3:27 PM

All replies

  • User351619809 posted

    I figured it out. It took me whole day though, If anyone wants to know, I can paste my sql stored proc here.

    Friday, October 28, 2011 5:50 PM
  • User269602965 posted

    Please do,

    educational to users of this forum

    Saturday, October 29, 2011 5:43 PM