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
