Huh...and that runs slow?
Imagine that.
;-)
Sweet mother of mystery, that is one gnarly query. Seems to have a lot
of repetition in it...could it be redone as a stored procedure?
Are you running it as a prepared statement?
Larry
On 2/10/06, Jonnalagadda, Sumithra <[EMAIL PROTECTED]> wrote:
> Here is the query
>
>
>
> SELECT PTI.PARTY_ID AS "CUSTID",
> PTI.CMA_CUSTOMER_NUMBER AS "CUSTNUM",
> PTI.SOURCE_ID AS "CUSTSOURCE",
> PTI.PARTY_CLASS_CD AS "CLASS",
> (SELECT CMAPSV.STATUS_VALUE
> FROM type3 CMAPS,
> type4 CMAPSV
> WHERE PTI.PARTY_TYPE_INSTANCE_ID = CMAPS.PARTY_TYPE_INSTANCE_ID
> AND CMAPS.PARTY_STATUS_TYPE_ID = 1
> AND CMAPS.PARTY_STATUS_VALUE_ID = CMAPSV.PARTY_STATUS_VALUE_ID
> AND (NVL(CMAPS.PEND_LIST_STATUS,
> 'N') = 'N')
> AND (TRD.CURR_DT >= CMAPS.FROM_DT
> AND TRD.CURR_DT <= CMAPS.TO_DT)
> AND ROWNUM = 1) AS "CUSTSTATUS",
> PTI.TYPE_NAME AS "CUSTTYPENAME",
> PTI.MASTER_PARTY_TYPE_ID AS "CUST_SUPER_TYPE_ID",
> PTI.PARTY_TYPE_ID AS "CUSTTYPE",
> (SELECT AU3.LINE_1 || '~' || AU3.CITY || '~' || AU3.STATE || '~' ||
> AU3.POSTAL_CODE
> FROM MV_type4 AU3
> WHERE AU3.PARTY_ID = PTI.PARTY_ID
> AND (NVL(AU3.PEND_LIST_STATUS,
> 'N') = 'N')
> AND ((TRD.CURR_DT >= AU3.ADDRUSG_FROM_DT
> AND TRD.CURR_DT <= AU3.ADDRUSG_TO_DT))
> AND AU3.ADDR_USAGE_TYPE_ID = '83'
> AND ((TRD.CURR_DT >= AU3.ADDR_FROM_DT
> AND TRD.CURR_DT <= AU3.ADDR_TO_DT))
> AND ROWNUM = 1) AS ADDRESS,
> (SELECT FIRST_NAME || '~' || MIDDLE_NAME || '~' || LAST_NAME
> FROM PERSON
> WHERE PERSON.PARTY_ID = PTI.PARTY_ID) AS "NAME",
> (SELECT CC.CLASSIF_CODE_NAME
> FROM type5 PC,
> type6 CC
> WHERE PTI.PARTY_ID = PC.PARTY_ID
> AND PTI.PARTY_TYPE_ID = PC.PARTY_TYPE_ID
> AND (TRD.CURR_DT >= PC.FROM_DT
> AND TRD.CURR_DT <= PC.TO_DT)
> AND (NVL(PC.PEND_LIST_STATUS,
> 'N') = 'N')
> AND PC.CLASSIF_CODE_ID = CC.CLASSIF_CODE_ID
> AND CC.CLASSIF_TYPE_ID = '7'
> AND ROWNUM = 1) AS "Primary Specialty",
> (SELECT CC.CLASSIF_CODE_NAME
> FROM type5 PC,
> type6 CC
> WHERE PTI.PARTY_ID = PC.PARTY_ID
> AND PTI.PARTY_TYPE_ID = PC.PARTY_TYPE_ID
> AND (TRD.CURR_DT >= PC.FROM_DT
> AND TRD.CURR_DT <= PC.TO_DT)
> AND (NVL(PC.PEND_LIST_STATUS, 'N') = 'N')
> AND PC.CLASSIF_CODE_ID = CC.CLASSIF_CODE_ID
> AND CC.CLASSIF_TYPE_ID = '132'
> AND ROWNUM = 1) AS "CAM_FLAG",
> (SELECT CC.CLASSIF_CODE_NAME
> FROM type5 PC,
> type6 CC
> WHERE PTI.PARTY_ID = PC.PARTY_ID
> AND PTI.PARTY_TYPE_ID = PC.PARTY_TYPE_ID
> AND (TRD.CURR_DT >= PC.FROM_DT
> AND TRD.CURR_DT <= PC.TO_DT)
> AND (NVL(PC.PEND_LIST_STATUS,
> 'N') = 'N')
> AND PC.CLASSIF_CODE_ID = CC.CLASSIF_CODE_ID
> AND CC.CLASSIF_TYPE_ID = '12'
> AND ROWNUM = 1) AS "MODEL",
> (SELECT CC.CLASSIF_CODE_NAME
> FROM type5 PC,
> type6 CC
> WHERE PTI.PARTY_ID = PC.PARTY_ID
> AND PTI.PARTY_TYPE_ID = PC.PARTY_TYPE_ID
> AND (TRD.CURR_DT >= PC.FROM_DT
> AND TRD.CURR_DT <= PC.TO_DT)
> AND (NVL(PC.PEND_LIST_STATUS,
> 'N') = 'N')
> AND PC.CLASSIF_CODE_ID = CC.CLASSIF_CODE_ID
> AND CC.CLASSIF_TYPE_ID = '12'
> AND ROWNUM = 1) AS "PROF_CLASS",
> (SELECT PR.REFERENCE_VALUE
> FROM PARTY_REFERENCE PR
> WHERE PTI.PARTY_ID = PR.PARTY_ID
> AND PR.PARTY_TYPE_ID = PTI.PARTY_TYPE_ID
> AND (TRD.CURR_DT >= PR.FROM_DT
> AND TRD.CURR_DT <= PR.TO_DT)
> AND (NVL(PR.PEND_LIST_STATUS,
> 'N') = 'N')
> AND PR.REF_TYPE_ID = '108'
> AND ROWNUM = 1) AS "IMS ID"
> FROM MV_TYPE1 PTI,
> TAB__RUN_TYPE2 TRD
> WHERE (TRD.CURR_DT >= PTI.FROM_DT
> AND TRD.CURR_DT <= PTI.TO_DT)
> AND (NVL(PTI.PEND_LIST_STATUS,
> 'N') = 'N')
> AND PTI.PARTY_TYPE_GROUP_ID IN ('1',
> '2')
> AND (PTI.CMA_CUSTOMER_NUMBER IS NULL
> OR NOT PTI.CMA_CUSTOMER_NUMBER = 1)
> AND PTI.PARTY_TYPE_ID = ('14')
> AND EXISTS (SELECT 1
> FROM PARTY_CLASSIF PC
> WHERE PC.PARTY_ID = PTI.PARTY_ID
> AND PC.PARTY_TYPE_ID = PTI.PARTY_TYPE_ID
> AND (NVL(PC.PEND_LIST_STATUS,
> 'N') = 'N')
> AND NOT (PC.FROM_DT >= '31-DEC-4000'
> OR PC.TO_DT <= '2-FEB-2006')
> AND (PC.CLASSIF_CODE_ID = ('1951')))
> ORDER BY TO_NUMBER("CUSTNUM") ASC
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
