Skip to Main Content
  • Questions

  • How to avoid multiple scanning of same tables again and again

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ashish.

Asked: September 04, 2017 - 7:24 am UTC

Last updated: September 04, 2017 - 1:33 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom

This query is scanning the below same table again and again and hence a
Table SALES_CASE is referenced 5 times in this query. This table has 3 Million rows.
Table MDM_KC_ORGANIZATION is referenced 5 times. This table has 300,000 records.
Table sor.person is referenced 3 times. rowcount 4.5 lakh
Table supcus is referenced 2 times with rowcount of 1.8 M.
Table supcus attribute is used 2 times with rowcount of 3.5 M.

Could you please provide a way to be developed to scan that once.please see the query below-

Query starts-----
SELECT /*+ PARALLEL(4) */ /* SL_SALES_CASE_S Base sales case data (type S) */
CAST(PREKEY.BEG_CAL_KEY AS NUMBER(38,0)) AS BEG_CAL_KEY,
CAST(PREKEY.END_CAL_KEY AS NUMBER(38,0)) AS END_CAL_KEY,
CAST('W' AS CHAR(1 CHAR)) AS SUMMARY_TYPE,
CAST('S' AS CHAR(1 CHAR)) AS LINE_TYPE,
CAST(SORSCA.SRCSYS_PRIMARY_ID AS VARCHAR2(50 CHAR)) AS SALES_CASE_ID,
CAST('0' AS VARCHAR2(50 CHAR)) AS SALES_CASE_ITEM_ID,
CAST(COMDIV.DIVISION_CODE AS VARCHAR2(50 CHAR)) AS DIVISION_CODE,
CAST(CASE
  WHEN SORLEG.MDM_KC_ORGANIZATION_KEY>0 THEN SORLEG.MDM_KC_ORGANIZATION_ID
  ELSE '0'
END AS VARCHAR2(50 CHAR)) AS LEGAL_COMPANY_CODE,
CAST(CASE
  WHEN SORSAT.MDM_KC_ORGANIZATION_KEY<>0 THEN SORSAT.MDM_KC_ORGANIZATION_ID
  WHEN SORBRA.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBRA.MDM_KC_ORGANIZATION_ID
  WHEN SORDIS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORDIS.MDM_KC_ORGANIZATION_ID
  WHEN SORBUS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBUS.MDM_KC_ORGANIZATION_ID
  ELSE '0'
END AS VARCHAR2(50 CHAR)) AS ORGANIZATION_CODE,
CAST(SORBUS.MDM_KC_ORGANIZATION_ID AS VARCHAR2(50 CHAR)) AS BUSINESS_ORGANIZATION_CODE,
CAST(SORPER.SRCSYS_PRIMARY_ID AS VARCHAR2(50 CHAR)) AS OWNER_PERSON_ROW_ID,
CAST(NVL(SORPER.AD_ID,'0') AS VARCHAR2(50 CHAR)) AS OWNER_PERSON_AD_ID,
CAST(NVL(ATTPERINA.ATTR_FLAG,0) AS NUMBER(38,0)) AS OWNER_PERSON_INACT_FLAG,
CAST(SORCUS.SRCSYS_PRIMARY_ID AS VARCHAR2(50 CHAR)) AS CUSTOMER_ROW_ID,
CAST(SORCPT.SRCSYS_PRIMARY_ID AS VARCHAR2(50 CHAR)) AS COMPETITOR_ROW_ID,
CAST(DESCTY.COUNTRY_CODE AS CHAR(2 CHAR)) AS DESTINATION_COUNTRY_CODE,
CAST(CUSCTY.COUNTRY_CODE AS CHAR(2 CHAR)) AS CUSTOMER_COUNTRY_CODE,
CAST(SCASPC.SP_CODE AS VARCHAR2(10 CHAR)) AS SCASE_SP_CODE,
CAST('N/A' AS VARCHAR2(10 CHAR)) AS PLINE_SP_CODE,
CAST(CUSSMA.SMA_CODE AS VARCHAR2(30 CHAR)) AS CUSTOMER_SMA_CODE,
CAST(SCASMA.SMA_CODE AS VARCHAR2(30 CHAR)) AS SCASE_SMA_CODE,
CAST('0' AS VARCHAR2(30 CHAR)) AS PLINE_SMA_CODE,
CAST(SORSCA.SRCSYS_CREATE_CAL_KEY AS NUMBER(38,0)) AS CREATED_CAL_KEY,
CAST(SORSCA.OFFER_UPDATE_CAL_KEY AS NUMBER(38,0)) AS UPDATED_CAL_KEY,
CAST(SORSCA.DECLINE_CAL_KEY AS NUMBER(38,0)) AS DECLINE_CAL_KEY,
CAST(SORSCA.CANCEL_CAL_KEY AS NUMBER(38,0)) AS CANCEL_CAL_KEY,
CAST(SORSCA.LOST_CAL_KEY AS NUMBER(38,0)) AS LOST_CAL_KEY,
CAST(SORSCA.DECISION_CAL_KEY AS NUMBER(38,0)) AS DECISION_CAL_KEY,
CAST(SORSCA.INQUIRY_CAL_KEY AS NUMBER(38,0)) AS INQUIRY_CAL_KEY,
CAST(SORSCA.CHECK_CAL_KEY AS NUMBER(38,0)) AS CHECK_CAL_KEY,
CAST(SORSCA.VALID_UNTIL_CAL_KEY AS NUMBER(38,0)) AS VALID_UNTIL_CAL_KEY,
CAST(SORSCA.EXPECTED_DELIVERY_CAL_KEY AS NUMBER(38,0)) AS EXPECTED_DELIVERY_CAL_KEY,
CAST(SORSCA.INSTALL_CAL_KEY AS NUMBER(38,0)) AS INSTALL_CAL_KEY,
CAST(SORSCA.EXPECTED_OFFER_CAL_KEY AS NUMBER(38,0)) AS EXPECTED_OFFER_CAL_KEY,
CAST(SORSCA.ORDER_RECEIVED_CAL_KEY AS NUMBER(38,0)) AS ORDER_RECEIVED_CAL_KEY,
CAST(NVL(OFFDAY.STAGE_OFFER_CAL_KEY,0) AS NUMBER(38,0)) AS STAGE_OFFER_CAL_KEY,
CAST(NVL(HOTDAY.STAGE_HOT_OFFER_CAL_KEY,0) AS NUMBER(38,0)) AS STAGE_HOT_OFFER_CAL_KEY,
CAST(SORSCA.CHECK_CAL_KEY AS NUMBER(38,0)) AS NEXT_CUST_COMM_CAL_KEY,
CAST(SORSCA.CLOSED_CAL_KEY AS NUMBER(38,0)) AS CLOSED_CAL_KEY,
CAST(SORSCA.REOPENED_CAL_KEY AS NUMBER(38,0)) AS REOPENED_CAL_KEY,
CAST(SORSCA.HIDDEN_FLAG AS NUMBER(38,0)) AS HIDDEN_FLAG,
CAST(1 AS NUMBER(38,0)) AS MAIN_SCASE_LINE_FLAG,
CAST(CASE
  WHEN SORSAT.CATEGORY='Alpha' THEN 1
  WHEN SORBRA.CATEGORY='Alpha' THEN 1
  WHEN SORDIS.CATEGORY='Alpha' THEN 1
  WHEN SORBUS.CATEGORY='Alpha' THEN 1
  ELSE 0
END AS NUMBER(38,0)) AS ALPHA_FLAG,
CAST(SORSCA.REOPENED_FLAG AS NUMBER(38,0)) AS REOPENED_FLAG,
CAST(SORSCA.LLE_SALES_CASE_FLAG AS NUMBER(38,0)) AS LLE_SALES_CASE_FLAG,
CAST(COMDIV.DIVISION_NAME AS VARCHAR2(200 CHAR)) AS DIVISION_NAME,
CAST(CASE
  WHEN SORSAT.MDM_KC_ORGANIZATION_KEY<>0 THEN SORSAT.MDM_KC_ORGANIZATION_NAME
  WHEN SORBRA.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBRA.MDM_KC_ORGANIZATION_NAME
  WHEN SORDIS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORDIS.MDM_KC_ORGANIZATION_NAME
  WHEN SORBUS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBUS.MDM_KC_ORGANIZATION_NAME
  ELSE ' '
END AS VARCHAR2(200 CHAR)) AS ORGANIZATION_NAME,
CAST(NVL(SORPER.FIRST_NAME,' ') AS VARCHAR2(50 CHAR)) AS OWNER_PERSON_FIRST_NAME,
CAST(NVL(SORPER.LAST_NAME,' ') AS VARCHAR2(50 CHAR)) AS OWNER_PERSON_LAST_NAME,
CAST(SORCUS.SUPCUS_NUMBER AS VARCHAR2(50 CHAR)) AS CUSTOMER_NUMBER,
CAST(SORCUS.SUPCUS_NAME AS VARCHAR2(100 CHAR)) AS CUSTOMER_NAME,
CAST(SORCPT.SUPCUS_NUMBER AS VARCHAR2(50 CHAR)) AS COMPETITOR_NUMBER,
CAST(SORCPT.SUPCUS_NAME AS VARCHAR2(100 CHAR)) AS COMPETITOR_NAME,
CAST(DESCTY.COUNTRY_NAME AS VARCHAR2(50 CHAR)) AS DESTINATION_COUNTRY_NAME,
CAST(CUSCTY.COUNTRY_NAME AS VARCHAR2(50 CHAR)) AS CUSTOMER_COUNTRY_NAME,
CAST(SCASTA.STATUS AS VARCHAR2(20 CHAR)) AS SCASE_STATUS,
CAST(STGSTA.STATUS AS VARCHAR2(20 CHAR)) AS STAGE_STATUS,
CAST(STGOST.STATUS AS VARCHAR2(20 CHAR)) AS STAGE_STATUS_ORIG,
CAST(' ' AS VARCHAR2(20 CHAR)) AS PLINE_STATUS,
CAST(SCASTG.STAGE_NAME AS VARCHAR2(50 CHAR)) AS STAGE,
CAST(SCOSTG.STAGE_NAME AS VARCHAR2(50 CHAR)) AS STAGE_ORIG,
CAST(SORSCA.STAGE_ORDNUM AS NUMBER(38,0)) AS STAGE_ORDNUM,
CAST(SORSCO.STAGE_ORDNUM AS NUMBER(38,0)) AS STAGE_ORDNUM_ORIG,
CAST(NVL(SCAPHA.PHASE_NAME,' ') AS VARCHAR2(50 CHAR)) AS PHASE,
CAST(SCAREA.REASON AS VARCHAR2(20 CHAR)) AS REASON,
CAST(SCALSO.LEAD_SOURCE AS VARCHAR2(200 CHAR)) AS LEAD_SOURCE,
CAST(SCATYP.TYPE AS VARCHAR2(20 CHAR)) AS SCASE_TYPE,
CAST(RNWTYP.TYPE AS VARCHAR2(20 CHAR)) AS RENEWAL_TYPE,
CAST(NVL(SCASPC.DESCRIPTION,' ') AS VARCHAR2(250 CHAR)) AS SCASE_SP_DESCRIPTION,
CAST(' ' AS VARCHAR2(250 CHAR)) AS PLINE_SP_DESCRIPTION,
CAST(' ' AS VARCHAR2(10 CHAR)) AS PRODUCT_FAMILY_NAME,
CAST(CUSSMA.SMA_DESCRIPTION AS VARCHAR2(50 CHAR)) AS CUSTOMER_SMA_DESCR,
CAST(SCASMA.SMA_DESCRIPTION AS VARCHAR2(50 CHAR)) AS SCASE_SMA_DESCR,
CAST(' ' AS VARCHAR2(50 CHAR)) AS PLINE_SMA_DESCR,
CAST(SORSCA.PROJECT_PROBABILITY_PCT AS NUMBER(22,7)) AS PROJECT_PROBABILITY_PCT,
CAST(SORSCA.KC_PROBABILITY_PCT AS NUMBER(22,7)) AS KC_PROBABILITY_PCT,
CAST(SORSCA.CMII AS NUMBER(22,7)) AS SCASE_CMII,
CAST(0 AS NUMBER(22,7)) AS PLINE_CMII,
CAST(SORSCA.CURRENCY_CODE_TRN AS CHAR(3 CHAR)) AS SCASE_CURRENCY_CODE_TRN,
CAST(SORSCO.VALUE_TRN*NVL(EURRAT.EUR_RATE,0) AS NUMBER(22,7)) AS CREATION_VALUE_EUR,
CAST(SORSCO.VALUE_TRN AS NUMBER(22,7)) AS CREATION_VALUE_TRN,
CAST(SORSCA.VALUE_TRN*NVL(EURRAT.EUR_RATE,0) AS NUMBER(22,7)) AS SCASE_VALUE_EUR,
CAST(SORSCA.VALUE_TRN AS NUMBER(22,7)) AS SCASE_VALUE_TRN,
CAST(0 AS NUMBER(22,7)) AS PLINE_VALUE_EUR,
CAST(0 AS NUMBER(22,7)) AS PLINE_VALUE_TRN,
CAST('0' AS VARCHAR2(50 CHAR)) AS PLINE_NUMBER,
CAST(0 AS NUMBER(22,7)) AS PLINE_CAPACITY,
CAST(0 AS NUMBER(22,7)) AS PLINE_SPAN,
CAST(0 AS NUMBER(22,7)) AS PLINE_QTY,
CASE
  WHEN SORSAT.CATEGORY='Alpha' THEN CAST(SORSCA.PROJECT_DESC AS VARCHAR2(100 CHAR))
  WHEN SORBRA.CATEGORY='Alpha' THEN CAST(SORSCA.PROJECT_DESC AS VARCHAR2(100 CHAR))
  WHEN SORDIS.CATEGORY='Alpha' THEN CAST(SORSCA.PROJECT_DESC AS VARCHAR2(100 CHAR))
  WHEN SORBUS.CATEGORY='Alpha' THEN CAST(SORSCA.PROJECT_DESC AS VARCHAR2(100 CHAR))
  ELSE 'N/A'
END  AS PROJECT_DESC,
CAST(SORSCA.NAME AS VARCHAR2(100 CHAR)) AS SCASE_NAME,
CAST(NVL(SORSCA.DESCRIPTION,' ') AS VARCHAR2(2000 CHAR)) AS SCASE_DESCRIPTION,
CAST(NVL(SORSCA.COMMENTS,' ') AS VARCHAR2(250 CHAR)) AS SCASE_COMMENTS,
CAST(' ' AS VARCHAR2(250 CHAR)) AS PLINE_DESCRIPTION,
CAST(SORHIP.SRCSYS_PRIMARY_ID AS VARCHAR2(50 CHAR)) AS HIS_OWNER_ROW_ID,
CAST(COMHSR.TYPE AS VARCHAR2(20 CHAR)) AS HIS_OWNER_SALES_ROLE,
CAST(SORCRP.SRCSYS_PRIMARY_ID AS VARCHAR2(50 CHAR)) AS CREATOR_ROW_ID,
CAST(NVL(CUSSSG.ATTR_STRING,'Unknown') AS VARCHAR2(20 CHAR)) AS HIS_CUSTOMER_SEGMENT,
CAST(NVL(CUSREL.ATTR_STRING,'Unknown') AS VARCHAR2(20 CHAR)) AS HIS_CUSTOMER_RELATION,
CAST(NVL(SORCMP.SRCSYS_PRIMARY_ID,'0') AS VARCHAR2(50 CHAR)) AS CAMPAIGN_ID,
CAST(NVL(CSMPSTS.ATTR_STRING,'Unknown') AS VARCHAR2(20 CHAR)) AS CAMPAIGN_STATUS,
CAST(NVL(COMORG.BRAND_CODE,'0') AS VARCHAR2(50 CHAR)) AS BRAND_CODE,
CAST(CASE WHEN SORSCA.LIST_PRICE_TOTAL_TRN=0 THEN 0 ELSE ((SORSCA.LIST_PRICE_TOTAL_TRN-SORSCA.VALUE_TRN)/SORSCA.LIST_PRICE_TOTAL_TRN*100) END AS NUMBER(22,7)) AS DISCOUNT_PCT,
CAST(PREKEY.SPLIT_WEEK_FLAG as NUMBER(38,0)) AS SPLIT_WEEK_FLAG,
CAST(CASE
  WHEN SCASTA.STATUS='Active' THEN SCASTG.STAGE_NAME
  WHEN SCASTA.STATUS='Won Offer' THEN SCASTG.STAGE_NAME
  ELSE SCASTA.STATUS
END AS VARCHAR2(50 CHAR)) AS SCASE_DERIVED_STATUS,
CAST(SORSCA.COMPETITOR_PRICE_TRN AS NUMBER(22,7)) AS COMPETITOR_PRICE_TRN,
CAST(SORSCA.COMPETITOR_PRICE_EUR AS NUMBER(22,7)) AS COMPETITOR_PRICE_EUR,
CAST(CASE
  WHEN SORSAT.MDM_KC_ORGANIZATION_KEY<>0 THEN SORSAT.INACTIVE_FLAG
  WHEN SORBRA.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBRA.INACTIVE_FLAG
  WHEN SORDIS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORDIS.INACTIVE_FLAG
  WHEN SORBUS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBUS.INACTIVE_FLAG
  ELSE 0
END AS NUMBER(38,0)) AS ORGANIZATION_INACT_FLAG,
CAST(NVL(COMDIV.INACTIVE_FLAG,0) AS NUMBER(38,0)) AS DIVISION_INACT_FLAG
-- Keys for this resolution
FROM
SALES.SL_SALES_CASE_TMPS_W PREKEY
-- The sales case
JOIN
SOR.SALES_CASE SORSCA ON
SORSCA.SALES_CASE_KEY=PREKEY.SALES_CASE_KEY
-- The original sales case
JOIN
SOR.SALES_CASE SORSCO ON
SORSCO.SALES_CASE_KEY=PREKEY.ORIGINAL_CASE_KEY
-- Current sales case
JOIN
SOR.SALES_CASE SORSCC ON
SORSCC.SALES_CASE_KEY=PREKEY.CURRENT_CASE_KEY
-- Division
JOIN
COMMON.DIVISION COMDIV ON
COMDIV.DIVISION_KEY=SORSCA.DIVISION_KEY
-- Legal company
JOIN
SOR.MDM_KC_ORGANIZATION SORLEG ON
SORLEG.MDM_KC_ORGANIZATION_KEY=SORSCA.LEGAL_COMPANY_KEY
-- Business organization
JOIN
SOR.MDM_KC_ORGANIZATION SORBUS ON
SORBUS.MDM_KC_ORGANIZATION_KEY=SORSCA.BUSINESS_ORGANIZATION_KEY
-- District organization
JOIN
SOR.MDM_KC_ORGANIZATION SORDIS ON
SORDIS.MDM_KC_ORGANIZATION_KEY=SORSCA.DISTRICT_KEY
-- Branch organization
JOIN
SOR.MDM_KC_ORGANIZATION SORBRA ON
SORBRA.MDM_KC_ORGANIZATION_KEY=SORSCA.BRANCH_KEY
-- Satellite organization
JOIN
SOR.MDM_KC_ORGANIZATION SORSAT ON
SORSAT.MDM_KC_ORGANIZATION_KEY=SORSCA.SATELLITE_KEY
-- Common organization for brand
LEFT JOIN
COMMON.ORGANIZATION COMORG ON
COMORG.ORGANIZATION_CODE=(CASE
  WHEN SORSAT.MDM_KC_ORGANIZATION_KEY<>0 THEN SORSAT.MDM_KC_ORGANIZATION_ID
  WHEN SORBRA.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBRA.MDM_KC_ORGANIZATION_ID
  WHEN SORDIS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORDIS.MDM_KC_ORGANIZATION_ID
  WHEN SORBUS.MDM_KC_ORGANIZATION_KEY<>0 THEN SORBUS.MDM_KC_ORGANIZATION_ID
  ELSE '0'
END)
-- Owner person
JOIN
SOR.PERSON SORPER ON
SORPER.PERSON_KEY=SORSCC.OWNER_PERSON_KEY
-- Owner Person inactive
LEFT OUTER JOIN
SOR.PERSON_ATTRIBUTE ATTPERINA ON
ATTPERINA.PERSON_KEY=SORPER.PERSON_KEY AND
ATTPERINA.ATTR_NAME='Inactive' AND
ATTPERINA.CURRENT_FLAG=1 AND
ATTPERINA.EFF_END_DATE IS NULL
-- Historic Owner person
JOIN
SOR.PERSON SORHIP ON
SORHIP.PERSON_KEY=SORSCA.OWNER_PERSON_KEY
-- Historic Owner Person Sales Role
JOIN
COMMON.TYPE_VALUE COMHSR ON
COMHSR.TYPE_KEY=SORSCA.SALES_ROLE_TYPE_KEY
-- Creator person
JOIN
SOR.PERSON SORCRP ON
SORCRP.PERSON_KEY=SORSCC.CREATOR_PERSON_KEY
-- Campaign
JOIN
SOR.CAMPAIGN SORCMP ON
SORCMP.CAMPAIGN_KEY=SORSCA.CAMPAIGN_KEY
-- Historic campaign status
LEFT OUTER JOIN (
SELECT
  CAMPAIGN_KEY,
  MAX(ATTR_STRING) AS ATTR_STRING,
  TO_NUMBER(TO_CHAR(EFF_START_DATE,'YYYYMMDD')) AS BEGIN_DAY,
  NVL(TO_NUMBER(TO_CHAR(EFF_END_DATE,'YYYYMMDD')),20301231) AS END_DAY
FROM
  SOR.CAMPAIGN_ATTRIBUTE
WHERE
  ATTR_NAME='STATUS'
GROUP BY
  CAMPAIGN_KEY,
  EFF_START_DATE,
  EFF_END_DATE
) CSMPSTS ON
CSMPSTS.CAMPAIGN_KEY=SORSCA.CAMPAIGN_KEY AND
CSMPSTS.BEGIN_DAY<=PREKEY.END_CAL_KEY AND
CSMPSTS.END_DAY>PREKEY.END_CAL_KEY
-- Customer
JOIN
SOR.SUPCUS SORCUS ON
SORCUS.SUPCUS_KEY=CASE
  WHEN SORSCA.SOLD_TO_KEY<>0 THEN SORSCA.SOLD_TO_KEY
  WHEN SORSCA.CUSTOMER_KEY<>0 THEN SORSCA.CUSTOMER_KEY
  ELSE 0
END
-- Historic customer segment
LEFT OUTER JOIN (
SELECT
  SUPCUS_KEY,
  MAX(ATTR_STRING) AS ATTR_STRING,
  TO_NUMBER(TO_CHAR(EFF_START_DATE,'YYYYMMDD')) AS BEGIN_DAY,
  NVL(TO_NUMBER(TO_CHAR(EFF_END_DATE,'YYYYMMDD')),20301231) AS END_DAY
FROM
  SOR.SUPCUS_ATTRIBUTE
WHERE
  ATTR_NAME='Customer Segment'
GROUP BY
  SUPCUS_KEY,
  EFF_START_DATE,
  EFF_END_DATE
) CUSSSG ON
CUSSSG.SUPCUS_KEY=SORSCA.CUSTOMER_KEY AND
CUSSSG.BEGIN_DAY<=PREKEY.END_CAL_KEY AND
CUSSSG.END_DAY>PREKEY.END_CAL_KEY
-- Historic customer relation
LEFT OUTER JOIN (
SELECT
  SUPCUS_KEY,
  MAX(ATTR_STRING) AS ATTR_STRING,
  TO_NUMBER(TO_CHAR(EFF_START_DATE,'YYYYMMDD')) AS BEGIN_DAY,
  NVL(TO_NUMBER(TO_CHAR(EFF_END_DATE,'YYYYMMDD')),20301231) AS END_DAY
FROM
  SOR.SUPCUS_ATTRIBUTE
WHERE
  ATTR_NAME='Relationship'
GROUP BY
  SUPCUS_KEY,
  EFF_START_DATE,
  EFF_END_DATE
) CUSREL ON
CUSREL.SUPCUS_KEY=SORSCA.CUSTOMER_KEY AND
CUSREL.BEGIN_DAY<=PREKEY.END_CAL_KEY AND
CUSREL.END_DAY>PREKEY.END_CAL_KEY
-- Competitor
JOIN
SOR.SUPCUS SORCPT ON
SORCPT.SUPCUS_KEY=SORSCA.COMPETITOR_KEY
-- Destination country
JOIN
COMMON.COUNTRY DESCTY ON
DESCTY.COUNTRY_KEY=SORSCA.DESTINATION_COUNTRY_KEY
-- Customer country
JOIN
COMMON.COUNTRY CUSCTY ON
CUSCTY.COUNTRY_KEY=SORCUS.COUNTRY_KEY
-- Sales case SP
JOIN
COMMON.SP SCASPC ON
SCASPC.SP_KEY=SORSCA.SP_KEY
-- Customer SMA
JOIN
COMMON.SMA CUSSMA ON
CUSSMA.SMA_KEY=SORSCA.SMA_KEY
-- Sales case SMA
JOIN
COMMON.SMA SCASMA ON
SCASMA.SMA_KEY=SORSCA.SALES_CASE_SMA_KEY
-- Sales case status
JOIN
COMMON.STATUS SCASTA ON
SCASTA.STATUS_KEY=SORSCA.OFFER_STATUS_KEY
-- Sales case original status
JOIN
COMMON.STATUS SCOSTA ON
SCOSTA.STATUS_KEY=SORSCO.OFFER_STATUS_KEY
-- Stage status
JOIN
COMMON.STATUS STGSTA ON
STGSTA.STATUS_KEY=SORSCA.STAGE_STATUS_KEY
-- Stage status original
JOIN
COMMON.STATUS STGOST ON
STGOST.STATUS_KEY=SORSCO.STAGE_STATUS_KEY
-- Stage
JOIN
COMMON.STAGE SCASTG ON
SCASTG.STAGE_KEY=SORSCA.STAGE_KEY
-- Original Stage
JOIN
COMMON.STAGE SCOSTG ON
SCOSTG.STAGE_KEY=SORSCO.STAGE_KEY
-- Phase
JOIN
COMMON.PHASE SCAPHA ON
SCAPHA.PHASE_KEY=SORSCA.PHASE_KEY
-- Reason
JOIN
COMMON.REASON SCAREA ON
SCAREA.REASON_KEY=SORSCA.REASON_KEY
-- Leas source
JOIN
COMMON.LEAD_SOURCE SCALSO ON
SCALSO.LEAD_SOURCE_KEY=SORSCA.LEAD_SOURCE_KEY
-- Sales case type
JOIN
COMMON.TYPE_VALUE SCATYP ON
SCATYP.TYPE_KEY=SORSCA.OFFER_TYPE_KEY
-- Renewal type
JOIN
COMMON.TYPE_VALUE RNWTYP ON
RNWTYP.TYPE_KEY=SORSCA.RENEWAL_TYPE_KEY
-- Euro rate
LEFT OUTER JOIN
COMMON.CURRENCY_RATE EURRAT ON
EURRAT.CALENDAR_DATE_KEY=PREKEY.BEG_CAL_KEY AND
EURRAT.CURRENCY_CODE=SORSCA.CURRENCY_CODE_TRN AND
EURRAT.RATE_TYPE='STANDARD'
-- First date when stage is offer
LEFT OUTER JOIN (
SELECT
  MIN(CASE
   WHEN SCA.OFFER_UPDATE_CAL_KEY>0 THEN OFFER_UPDATE_CAL_KEY
   WHEN SCA.SRCSYS_UPDATE_CAL_KEY>0 THEN SRCSYS_UPDATE_CAL_KEY
   ELSE TO_NUMBER(TO_CHAR(SCA.UPDATED_DATE,'YYYYMMDD'))
  END) AS STAGE_OFFER_CAL_KEY,
  SCA.SRCSYS_PRIMARY_ID AS SALES_CASE_ID
FROM
  SOR.SALES_CASE SCA
JOIN
  COMMON.STAGE STG ON
  STG.STAGE_KEY=SCA.STAGE_KEY AND
  STG.STAGE_NAME='05 - Offer'
GROUP BY
  SCA.SRCSYS_PRIMARY_ID
) OFFDAY ON
OFFDAY.SALES_CASE_ID=SORSCA.SRCSYS_PRIMARY_ID AND
OFFDAY.STAGE_OFFER_CAL_KEY<=PREKEY.END_CAL_KEY
-- First date when stage is hot offer
LEFT OUTER JOIN (
SELECT
  MIN(CASE
   WHEN SCA.OFFER_UPDATE_CAL_KEY>0 THEN OFFER_UPDATE_CAL_KEY
   WHEN SCA.SRCSYS_UPDATE_CAL_KEY>0 THEN SRCSYS_UPDATE_CAL_KEY
   ELSE TO_NUMBER(TO_CHAR(SCA.UPDATED_DATE,'YYYYMMDD'))
 END) AS STAGE_HOT_OFFER_CAL_KEY,
  SCA.SRCSYS_PRIMARY_ID AS SALES_CASE_ID
FROM
  SOR.SALES_CASE SCA
JOIN
  COMMON.STAGE STG ON
  STG.STAGE_KEY=SCA.STAGE_KEY AND
  STG.STAGE_NAME='06 - Hot Offer'
GROUP BY
  SCA.SRCSYS_PRIMARY_ID
) HOTDAY ON
HOTDAY.SALES_CASE_ID=SORSCA.SRCSYS_PRIMARY_ID AND
HOTDAY.STAGE_HOT_OFFER_CAL_KEY<=PREKEY.END_CAL_KEY
WHERE
-- Remove alternative sales cases
(
  SORSCA.GROUP_PROJECT_ID = SORSCA.SRCSYS_PRIMARY_ID OR --Changed the logic of alternative project sales case due to change in Siebel logic (DW-3170)
  (SORSCA.GROUP_PROJECT_ID IS NULL AND SORSCA.PRIMARY_GROUP_PROJECT_ID IS NULL) 
 )
-- Remove reopened cases
--AND SORSCA.REOPENED_FLAG=0
---Query Ends

and Chris said...

¯\_(ツ)_/¯

Seriously.

That's a huge query with a lot going on. We've no idea:

- What question it answers
- The business meaning of the data
- Exactly what the relationships between the tables are (1:1? 1:M?)
- etc.

If you can't figure out how to rationalize this with access to the DDL, the functional requirements, knowledge of the business etc., what chance do we have?

For instance, I have no idea why the query has lots of case statements like this:

case
  when sorsat.category = 'Alpha'   then 1
  when sorbra.category = 'Alpha'   then 1
  when sordis.category = 'Alpha'   then 1
  when sorbus.category = 'Alpha'   then 1
  else 0
end


Without knowing what you're trying to do, how can we give effective help?

So we can help you, please:

- Break the problem down into smaller chunks. Give us the part you're stuck with. Make this as small as possible
- Give an overall written explanation of what the query is supposed to do
- Provide us with a complete test case. One that includes
- DDL (aka create table)
- Sample data (aka insert into)
- Expected output based on these. Ideally with several different examples

If you can shrink the problem and come back with all the information listed above, we'll see what we can do to help.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.