-- @C:\QU.SQL
-- Run all the script individually in HR83 environment before running make sure database link should exist.
SET ECHO ON FEEDBACK ON
DELETE FROM PSQRYBIND WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYCRITERIA WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYDEFN WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYEXPR WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYFIELD WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYRECORD WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYLINK WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYFIELD WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYSELECT WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
INSERT INTO PSQRYBIND
SELECT
OPRID
,QRYNAME
,BNDNAME
,BNDNUM
,FIELDNAME
,HDGTYPE
,HEADING
,FIELDTYPE
,LENGTH
,DECIMALPOS
,FORMAT
,EDITTABLE
,USECOUNT
,USEEDIT
FROM PSQRYBIND@fpra WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
where G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/
INSERT INTO PSQRYCRITERIA SELECT OPRID
,QRYNAME
,SELNUM
,CRTNUM
,COMBTYPE
,NEGATION
,LPARENLVL
,LCRTSELNUM
,LCRTFLDNUM
,CONDTYPE
,EXPRTYPE
,R1CRTSELNUM
,R1CRTFLDNUM
,R1CRTEXPNUM
,R2CRTSELNUM
,R2CRTFLDNUM
,R2CRTEXPNUM
,RPARENLVL , 0 FROM PSQRYCRITERIA@FPRA
WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@FPRA G
WHERE
G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/
INSERT INTO PSQRYDEFN
SELECT OPRID, QRYNAME, DESCR, VERSION,QRYTYPE, SELCOUNT, EXPCOUNT, BNDCOUNT,QRYVALID,SYSDATE,'BFRIDLEY',
' ',' ',sysdate,' ',sysdate,' ', ' ', ' ',DESCR
FROM PSQRYDEFN@fpra
WHERE QRYNAME in (Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
WHERE G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/
DECLARE
CURSOR TEMP_CURSOR IS SELECT *
FROM PSQRYEXPR@fpra
WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
WHERE G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
;
P TEMP_CURSOR%ROWTYPE
;
BEGIN
OPEN TEMP_CURSOR
;
LOOP
FETCH TEMP_CURSOR INTO P
;
EXIT WHEN TEMP_CURSOR%NOTFOUND
;
INSERT INTO PSQRYEXPR (
OPRID,
QRYNAME,
EXPNUM,
FIELDTYPE,
LENGTH,
DECIMALPOS,
USECOUNT,
EXPRESSIONTEXT)
VALUES(
P.OPRID,
P.QRYNAME,
P.EXPNUM,
P.FIELDTYPE,
P.LENGTH,
P.DECIMALPOS,
P.USECOUNT,
P.EXPRESSIONTEXT)
;
COMMIT
;
END LOOP
;
CLOSE TEMP_CURSOR
;
END
;
/
INSERT INTO PSQRYFIELD
SELECT
OPRID
,QRYNAME
,QRYFLDNAME
,SELNUM
,FLDNUM
,RECNAME
,FIELDNAME
,FLDRCDNUM
,FLDEXPNUM
,HDGTYPE
,HEADING
,COLUMNNUM
,GROUPBYNUM
,ORDERBYNUM
,ORDERBYDIR
,TTLTYPE
,SUBTTLNUM
,USECOUNT
,XLATTYPE
,XLATEXPRTYPE
,XLATFLDNUM
,XLATEXPNUM
,AGGREGATEFUNC
FROM PSQRYFIELD@fpra
WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
WHERE G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/
INSERT INTO PSQRYLINK SELECT * FROM PSQRYLINK@fpra
WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
WHERE G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/
INSERT INTO PSQRYRECORD SELECT * FROM PSQRYRECORD@fpra
WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
WHERE G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/
INSERT INTO PSQRYSELECT SELECT * FROM PSQRYSELECT@fpra
WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
WHERE G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/
COMMIT
/
Tuesday, December 11, 2007
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment