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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help SQL

RE: Help SQL

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Mon, 13 Aug 2001 12:38:03 -0700
Message-ID: <F001.003692AA.20010813124714@fatcity.com> 






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 join    WHERE 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 line            AND person.hpc_id  = pes.HPC_ID

==> added parens around        AND (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,'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: mbryan_at_bcharrispub.com

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: ListGuru_at_fatcity.com (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.com
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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: ListGuru_at_fatcity.com (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).


Received on Mon Aug 13 2001 - 14:38:03 CDT












Original text of this message













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