SQL Server Performance

converting oracle stored proc to sql

Discussion in 'SQL Server 2008 General Developer Questions' started by anjali5, Oct 27, 2011.

  1. anjali5 New Member

    Code:
    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;
       
    /
    
    
    Hello,

    I am very new to sql. I want to convert oracle stored proc to sql . Below is my code

    any help will be greatly appreciated.
  2. satya Moderator

    Welcome to the forums.
    I have edited the thread to appeal the code to look better.
    The question is not clear that what kind of error or issue you are getting after the conversion from PlSQL to TSQL?

Share This Page