AW: Looking for help - sql*loader and truncate
Hi Looks like I have to try that one, since truncate should be reasonably faster then replace. Thanks, Stefan -Ursprüngliche Nachricht- Von: Jacques Kilchoer [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 13. November 2003 20:54 An: Multiple recipients of list ORACLE-L Betreff: RE: Looking for help - sql*loader and truncate In that case you could create a procedure owned by the data owner that does the truncate, grant execute on the procedure to the data loader, and use SQL*Plus to call the truncate procedure before the SQL*load starts. > -Original Message- > Stefan Jahnke > > .. and there is another scenario to use replace. As you > mention it, that's > what we do, too ;). The package owner (who owns the > transformation packages) > also does the load, so we use replace here, since I didn't feel like > granting DROP ... to the package user or use the data owner > to do the load. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Looking for help - sql*loader and truncate
In that case you could create a procedure owned by the data owner that does the truncate, grant execute on the procedure to the data loader, and use SQL*Plus to call the truncate procedure before the SQL*load starts. > -Original Message- > Stefan Jahnke > > .. and there is another scenario to use replace. As you > mention it, that's > what we do, too ;). The package owner (who owns the > transformation packages) > also does the load, so we use replace here, since I didn't feel like > granting DROP ... to the package user or use the data owner > to do the load. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Help SQL
Miriam,
Looks like you missed a couple of things:
1). you do not need outer joins in the EXISTS clauses here - you just need
to know if
it EXISTS or NOT - it's ok for it to fail
2). looks like you are missing an additional AND clause in the EXISTS
clause - you did not
join it to the main PERSON table.
3). looks like you are missing a set of parens around all of the OR clauses
in the second
EXISTS clause.
hope this helps
DS_NPS.NOTES_INFO NOTES
WHERE((NOT EXISTS
(SELECT PES.HPC_ID
FROM DS_NPS.PERSON_EVENT_STATUS pes
==> remove outer joinWHERE person.HPC_ID = pes.HPC_ID AND
==> remove outer join 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
==> added this lineAND person.hpc_id = pes.HPC_ID
==> added parens aroundAND (nvl(pes.LAST_UPDATE_DATE, '01-Jan-1900')
<
==> all of the OR's 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')
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, August 13, 2001 4:21 PM
To: Multiple recipients of list ORACLE-L
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,'00'),10,'0')
PRIMARY_PHONE_NMBR,
RPAD(NVL(ADDR1322.PRIMARY_PHONE_NMBR,'00'),10,'0')
SECONDARY_PHONE_NMBR,
RPAD(NVL(DECODE(ADDR1087.SECONDARY_PHONE_NMBR,NULL,ADDR1338.PRIMARY_PHONE_NM
BR,ADDR1087.SECONDARY_PHONE_NMBR),'00'),10,'0') OTHER_PHONE,
PERSON.VIP_IND,
RPAD(NVL(TO_CHAR(PERSON.DATE_OF_BIRTH,'mmdd'),''),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, 'mmdd'),''),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,000),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 a
Help SQL
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,'00'),10,'0')
PRIMARY_PHONE_NMBR,
RPAD(NVL(ADDR1322.PRIMARY_PHONE_NMBR,'00'),10,'0')
SECONDARY_PHONE_NMBR,
RPAD(NVL(DECODE(ADDR1087.SECONDARY_PHONE_NMBR,NULL,ADDR1338.PRIMARY_PHONE_NM
BR,ADDR1087.SECONDARY_PHONE_NMBR),'00'),10,'0') OTHER_PHONE,
PERSON.VIP_IND,
RPAD(NVL(TO_CHAR(PERSON.DATE_OF_BIRTH,'mmdd'),''),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, 'mmdd'),''),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,000),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_NM
