Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Running plain SQL statements???

Running plain SQL statements???

From: Chris Drew <cdrew_at_maine.rr.com>
Date: Thu, 11 Feb 1999 13:58:30 -0500
Message-ID: <iKFw2.195$6d6.701@newsr2.maine.rr.com>


Can this SQL statement be stored and run as a stored procedure? Can it be done without re-writing using cursors, etc...?? All help is appreciated..

/* OLD - shortens certificate number length to 9 on non-bme business */
/* UPDATE REPOSIT.LTR_EXTRACT_TEMP SET FIELD_13 = SUBSTR(FIELD_13,1,9) */
/* WHERE FIELD_105 <> 'BME' */
/* */
/* COMMIT */
/* */
/* appends entire daily WinUCS letter extract to history table */
INSERT INTO REPOSIT.LTR_EXTRACT_INFO
SELECT * FROM REPOSIT.LTR_EXTRACT_TEMP
/

COMMIT
/
/* removes all records from table before next letter selection run */
DELETE FROM REPOSIT.LTR_MAIL_TO_INFO
/

COMMIT
/
/* appends 'attending' mailing data records to table for certain letter
types */
INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB, MAIL_TO_TYPE,
 MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY, MAIL_TO_STATE,
 MAIL_TO_ZIP_CD )
SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,  UPPER(FIELD_29), UPPER(FIELD_30), UPPER(FIELD_31), UPPER(FIELD_32),  UPPER(FIELD_33), FIELD_34
FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,  REPOSIT.LTR_LETTER_RECIPIENT_INFO
WHERE RECIPIENT_CD = 1
 AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR  AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
/
/* appends 'facility' mailing data records to table for certain letter types
*/
INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB, MAIL_TO_TYPE,
 MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY, MAIL_TO_STATE,
 MAIL_TO_ZIP_CD )
SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,  UPPER(FIELD_23), UPPER(FIELD_24), UPPER(FIELD_25), UPPER(FIELD_26),  UPPER(FIELD_27), FIELD_28
FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,  REPOSIT.LTR_LETTER_RECIPIENT_INFO
WHERE RECIPIENT_CD = 2
 AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR  AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
/
/* OLD - appends 'insurer' mailing data records to table for certain letter
types and */
/* where client code does not equal 'bme', this prevents printing letters to
BCBSME */
/* INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,

MAIL_TO_TYPE, */

/* MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
MAIL_TO_STATE, */

/* MAIL_TO_ZIP_CD ) */
/* SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD, */
/* UPPER(FIELD_200), UPPER(FIELD_213), UPPER(FIELD_214), UPPER(FIELD_215),
*/
/* UPPER(FIELD_216), FIELD_217 */
/* FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE, */
/* REPOSIT.LTR_LETTER_RECIPIENT_INFO */
/* WHERE RECIPIENT_CD = 3 */
/* AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR */
/* AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD */
/* AND FIELD_105 != 'BME' */
/* */
/* appends 'patient' mailing data records to table for certain letter types
*/
INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB, MAIL_TO_TYPE,
 MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY, MAIL_TO_STATE,
 MAIL_TO_ZIP_CD )
SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,  UPPER(FIELD_9||' '||FIELD_11), UPPER(FIELD_14), UPPER(FIELD_15),  UPPER(FIELD_16), UPPER(FIELD_17), FIELD_18 FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,  REPOSIT.LTR_LETTER_RECIPIENT_INFO
WHERE RECIPIENT_CD = 4
 AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR  AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
/
/* OLD - appends 'patient' mailing data records to table when patient name
and address */
/* data does not match subscriber's */
/* INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
MAIL_TO_TYPE, */
/* MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
MAIL_TO_STATE, */
/* MAIL_TO_ZIP_CD ) */
/* SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD, */
/* UPPER(FIELD_9||' '||FIELD_11), UPPER(FIELD_14), UPPER(FIELD_15), */
/* UPPER(FIELD_16), UPPER(FIELD_17), FIELD_18 */
/* FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE, */
/* REPOSIT.LTR_LETTER_RECIPIENT_INFO */
/* WHERE RECIPIENT_CD = 5 */
/* AND ((FIELD_9||FIELD_10||FIELD_11 != REPLACE(FIELD_126,' ')||FIELD_127)
*/
/* AND (FIELD_14||FIELD_16||FIELD_17||FIELD_18 !=
FIELD_129||FIELD_130||FIELD_131||FIELD_132)) */
/* AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR */
/* AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD */
/* */
/* appends 'physician' mailing data records to table for certain letter
types */
INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB, MAIL_TO_TYPE,
 MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY, MAIL_TO_STATE,
 MAIL_TO_ZIP_CD )
SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,  UPPER(FIELD_29), UPPER(FIELD_30), UPPER(FIELD_31), UPPER(FIELD_32),  UPPER(FIELD_33), FIELD_34
FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,  REPOSIT.LTR_LETTER_RECIPIENT_INFO
WHERE RECIPIENT_CD = 6
 AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR  AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
/
/* appends 'subscriber' mailing data records to table for certain letter
types */
INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB, MAIL_TO_TYPE,
 MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_CITY, MAIL_TO_STATE, MAIL_TO_ZIP_CD )
SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,  UPPER(FIELD_126)||' '||UPPER(FIELD_127), UPPER(FIELD_129), UPPER(FIELD_130),
 FIELD_131, FIELD_132
FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,  REPOSIT.LTR_LETTER_RECIPIENT_INFO
WHERE RECIPIENT_CD = 7
 AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR  AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
/

COMMIT
/
/* sets exception flag to 'n' where name, address, city, state or zip_cd
data is missing */
UPDATE REPOSIT.LTR_MAIL_TO_INFO
SET MAIL_TO_EXCEPTION = 'M'
WHERE EXTRACT_ROW_NB IN ( SELECT EXTRACT_ROW_NB     FROM REPOSIT.LTR_MAIL_TO_INFO
    WHERE  MAIL_TO_NAME IS NULL
     OR MAIL_TO_ADDRESS1 IS NULL
     OR MAIL_TO_CITY IS NULL
     OR MAIL_TO_STATE IS NULL
     OR MAIL_TO_ZIP_CD IS NULL)

/

COMMIT
/
/* sets exception flag to 'n' where plan code value is missing in
ltr_logo_info table */
UPDATE REPOSIT.LTR_MAIL_TO_INFO
SET MAIL_TO_EXCEPTION = 'P'
WHERE EXTRACT_ROW_NB IN ( SELECT EXTRACT_ROW_NB     FROM ( SELECT EXTRACT_ROW_NB, PLAN_CD
     FROM  REPOSIT.LTR_EXTRACT_TEMP, LTR_LOGO_INFO
     WHERE  FIELD_189 = PLAN_CD (+)
      AND PLAN_CD IS NULL))

/

COMMIT
/
Received on Thu Feb 11 1999 - 12:58:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US