Hello dear list,
yet again I need some brainpower. Hopefully someone can tell me how to
format this query.
Here's my problem. I'm trying to run the below query to get a record set of
those users that qualify to be phoned, but ONLY if they have never been
phoned before(by not existing on the PERSON_EVENT_STATUS table) OR if the
LAST_UPDATE_DATE of the person, address or degree table is greater than the
LAST_UPDATE_DATE on the PERSON_EVENT_STATUS table, which would indicate that
this records have been modified. My problem is formatting or the ORs, I seem
to get all the right records if they are not found on the
PERSON_EVENT_STATUS table, but if I rerun this query, I still get the same
records, although they now exists on the PERSON_EVENT_STATUS table. (What
I'm I doing wrong? Problem area in RED).
QUERY;
SELECT RPAD(PROJECT_EVENT.PROJECT_EVENT_ID,18) PROJECT_EVENT_ID,
RPAD(ORG.ORG_ID,18) ORG_ID,
RPAD(TRANSLATE(ORG.ORG_NAME,'1?|''+& *%,#!@_$123.4567832-','1'),100)
ORG_NAME,
RPAD(PERSON.HPC_ID,18) HPC_ID,
RPAD(PERSON.DATA_SET_ID,18) DATA_SET_ID,
RPAD(NVL(TMKT.SPLIT_CODE,' '),2) SPLIT_CODE,
RPAD(NVL(PERSON.PREFIX, ' '),20) PREFIX,
RPAD(PERSON.FIRST_NAME, 50) FIRST_NAME,
RPAD(TRANSLATE(PERSON.LAST_NAME,'1?|''+& *%,#"!@_$123.4567832-','1'),50)
LAST_NAME,
RPAD(NVL(PERSON.SUFFIX,' '),20) SUFFIX,
RPAD(NVL(ADDR1087.STREET_LINE_1,'
'),50) STREET_LINE_1,
RPAD(NVL(ADDR1087.STREET_LINE_2,'
'),50) STREET_LINE_2,
RPAD(NVL(ADDR1087.CITY, '
'),40)CITY,
RPAD(NVL(ADDR1087.STATE_CODE,' '),2) STATE_CODE,
RPAD(DECODE(ADDR1087.COUNTRY, 'Bermuda','BE','Canada','CA','United
States','US','USA','US',NULL,'US','US'), 2) COUNTRY,
RPAD(NVL(ADDR1087.ZIP,' '), 9) ZIP,
RPAD(NVL(ADDR1087.PRIMARY_PHONE_NMBR,'0000000000'),10,'0')
PRIMARY_PHONE_NMBR,
RPAD(NVL(ADDR1322.PRIMARY_PHONE_NMBR,'0000000000'),10,'0')
SECONDARY_PHONE_NMBR,
RPAD(NVL(DECODE(ADDR1087.SECONDARY_PHONE_NMBR,NULL,ADDR1338.PRIMARY_PHONE_NM
BR,ADDR1087.SECONDARY_PHONE_NMBR),'0000000000'),10,'0') OTHER_PHONE,
PERSON.VIP_IND,
RPAD(NVL(TO_CHAR(PERSON.DATE_OF_BIRTH,'mmddyyyy'),' '),8)
DATE_OF_BIRTH,
RPAD(NVL(PERSON.GENDER,' '),1) GENDER,
RPAD(NVL(PERSON.MARITAL_STATUS,' '), 2) MARITAL_STATUS,
RPAD(NVL(DEG1089.CLASS_YEAR,' '), 4) CLASS_YEAR,
PERSON.MAIL_RESPONDENT_IND,
PERSON.OLQ_RESPONDENT_IND,
PERSON.PREVIOUS_PURCHASER_IND,
RPAD(NVL(TO_CHAR(PROJECT.LCI_DATE, 'mmddyyyy'),' '),8) LCI_DATE,
RPAD(NVL(PERSON.MARKETING_CODE,' '),20)
MARKETING_CODE,
RPAD(NVL(TMKT.CALL_CENTER,' '), 2) CALL_CENTER,
RPAD(NVL(DEG1089.DEGREE_NAME, '
'),50) DEGREE_NAME,
RPAD(NVL(NOTES.NOTES_DESCR,'
'),100) NOTES_DESCR,
RPAD(NVL(TMKT.TOLL_FREE_NUMBER,8004553052),10,'0') TOLL_FREE_NUMBER,
Rpad(NVL(PROJECT.PROJECT_ID,0000000000000000000),18) project_id,
RPAD('FILLER',30) FILLER,
RPAD('FILLER',30) FILLER2,
RPAD('FILLER',30) FILLER3,
NVL(TMKT.CALL_CENTER,'VA')||RPAD('0',1)||NVL(TMKT.SPLIT_CODE,'1')||PROJECT.F
INANCE_ORG_CODE||RPAD('FILLER',16) LIST_NAME,
RPAD(NVL(PROJECT.FINANCE_ORG_CODE,' '),3) FINANCE_ORG_CODE
FROM
DS_NPS.ADDRESS addr1087,
DS_NPS.ADDRESS addr1322,
DS_NPS.ADDRESS addr1338,
DS_NPS.PERSON PERSON,
DS_NPS.DEGREE DEG1089,
DS_NPS.TMKT_SPLIT TMKT,
DS_NPS.DATA_SET DATA_SET,
DS_NPS.PROJECT PROJECT,
DS_NPS.PROJECT_EVENT PROJECT_EVENT,
DS_NPS.ORGANIZATION ORG,
DS_NPS.NOTES_INFO NOTES
WHERE((NOT EXISTS
(SELECT PES.HPC_ID
FROM DS_NPS.PERSON_EVENT_STATUS pes
WHERE person.HPC_ID = pes.HPC_ID (+) AND
pes.EVENT_REFERENCE_VALUE_ID (+) = 4116)) OR
(EXISTS
(SELECT pes.HPC_ID
FROM DS_NPS.PERSON_EVENT_STATUS pes
WHERE pes.EVENT_REFERENCE_VALUE_ID = 4116
AND pes.STATUS_REFERENCE_VALUE_ID = 1586 AND
nvl(pes.LAST_UPDATE_DATE, '01-Jan-1900') <
nvl(pes.LAST_UPDATE_DATE, '02-Jan-1900') OR
nvl(pes.LAST_UPDATE_DATE, '02-Jan-1900') <
nvl(DEG1089.LAST_UPDATE_DATE, '02-Jan-1900') OR
nvl(pes.LAST_UPDATE_DATE, '02-Jan-1900') <
nvl(addr1087.LAST_UPDATE_DATE, '02-Jan-1900') OR
nvl(pes.LAST_UPDATE_DATE, '02-Jan-1900') <
nvl(addr1322.LAST_UPDATE_DATE, '02-Jan-1900'))))
AND TRUNC(SYSDATE) BETWEEN TRUNC(PROJECT.OB_START_DATE -5) AND
TRUNC(PROJECT.LCI_DATE + 5)
AND PERSON.DUPLICATE_IND = 'N'
AND (TMKT.CALL_CENTER <> ' '
OR TMKT.CALL_CENTER IS NOT NULL)
AND (TMKT.SPLIT_CODE <> ' '
OR TMKT.SPLIT_CODE IS NOT NULL)
AND (PERSON.DO_NOT_CALL_STATUS = ' '
OR PERSON.DO_NOT_CALL_STATUS IS NULL)
AND PERSON.REQUESTED_OMIT_IND = 'N'
AND (NVL(addr1087.CITY,'*') NOT LIKE 'APO%'
AND NVL(addr1087.CITY,'*') NOT LIKE 'FPO%')
AND (addr1087.COUNTRY is null
or addr1087.COUNTRY IN('CA','BE','US')
AND (addr1087.PRIMARY_PHONE_NMBR is not null
or addr1322.PRIMARY_PHONE_NMBR is not null
or addr1087.PRIMARY_PHONE_NMBR NOT IN(0000000000,9999999999)
-- or addr1087.SECONDARY_PHONE_NMBR NOT
IN(0000000000,9999999999)
or addr1322.PRIMARY_PHONE_NMBR NOT IN(0000000000,9999999999)
or substr(addr1087.PRIMARY_PHONE_NMBR,1,1) NOT IN(0,1)
-- or substr(addr1087.SECONDARY_PHONE_NMBR,1,1) NOT IN(0,1)
or substr(addr1322.PRIMARY_PHONE_NMBR,1,1) NOT IN(0,1)
or LENGTH(addr1087.PRIMARY_PHONE_NMBR) = 10
or LENGTH(addr1322.PRIMARY_PHONE_NMBR) = 10))
-- or LENGTH(OTHER_PHONE_NMBR) = 10))
AND (PERSON.FIRST_NAME <> ' '
OR PERSON.FIRST_NAME IS NOT NULL)
AND (PERSON.LAST_NAME <> ' '
OR PERSON.LAST_NAME IS NOT NULL)
AND PERSON.VIP_IND = 'N'
AND (PERSON.DECEASED_STATUS = ' '
OR PERSON.DECEASED_STATUS IS NULL)
AND (PERSON.LOST_STATUS = ' '
OR PERSON.LOST_STATUS IS NULL)
AND PERSON.PURCHASER <> 'Y'
AND (PERSON.NON_MEMBER_STATUS = ' '
OR PERSON.NON_MEMBER_STATUS IS NULL)
AND PERSON.DISPOSITION_CODE NOT
IN('Z','D','SA','SG','NO','NA','LO','LA','LU','L8','OM')
AND person.HPC_ID = addr1087.HPC_ID (+)
AND addr1087.REFERENCE_VALUE_ROW_TYPE (+) = 1087
AND person.HPC_ID = addr1322.HPC_ID (+)
AND addr1322.REFERENCE_VALUE_ROW_TYPE (+) = 1322
AND person.HPC_ID = addr1338.HPC_ID (+)
AND addr1338.REFERENCE_VALUE_ROW_TYPE (+) = 1338
AND PERSON.HPC_ID = DEG1089.HPC_ID (+)
AND DEG1089.REFERENCE_VALUE_ROW_TYPE (+) = 1089
AND TMKT.TMKT_SPLIT = PERSON.TMKT_SPLIT(+)
AND PERSON.HPC_ID = NOTES.HPC_ID (+)
AND NOTES.reference_value_row_type (+) = 1234
AND ORG.ORG_ID (+) = DATA_SET.ORG_ID
AND DATA_SET.DATA_SET_ID = PROJECT.DATA_SET_ID (+)
AND PROJECT.PROJECT_ID = PROJECT_EVENT.PROJECT_ID (+)
AND PROJECT_EVENT.EVENT_REFERENCE_VALUE_ID = 4116
AND DATA_SET.DATA_SET_ID = person.DATA_SET_ID;
TIA,
Miriam Bryan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bryan, Miriam
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).