Solved

Why does embedded SELECT cause

Posted on 2013-02-01
3
318 Views
Last Modified: 2013-02-19
hello,

When I run this select by itself, it runs very quickly and the results are accurate, but when embedded in the subsequent query, the query runs so long that I end up canceling it. Can someone tell me what the probem is?

Thank you!

-- Pick up employee status and termination date, if available
SELECT cl.LAST_NAME,cl.CASE_NUMBER,cl.SSN, e.status, e.TERMINATION_DATE
    FROM        IDRDATA.DIM_HC_CLAIMANT_DETAIL cl
    LEFT JOIN IDRDATA.DIM_EMPLOYEE e
        on           cl.SSN = e.SSN
        AND        cl.EXPIRATION_DATE IS NULL
        AND        e.EXPIRATION_DATE IS NULL


SELECT DISTINCT
--NEW INFO
     Z5.AREA_NAME                          
,    Z5.DISTRICT_NAME                      
,    c.CASE_NUMBER                    
,    cl.SSN
,    CASE
           WHEN (emp.STATUS    is  null) THEN 'S'
           ELSE emp.STATUS
     END Employee_Status
,    CASE
           WHEN   (TO_CHAR(emp.TERMINATION_DATE, 'Mon DD, YYYY') IS NULL THEN 'Date Unavailable'
           ELSE TO_CHAR(emp.TERMINATION_DATE, Mon DD, YYYY')
     END Termination_dt                      
,    cl.LAST_NAME                          
,    cl.FIRST_NAME                        
,    cl.ADDRESS                            
,    cl.CITY                              
,    cl.STATE                            
,    cl.ZIP   claimant_zip_code              
,    z9.CITY  current_city                  
,    z9.STATE current_state                
,    comp.PAYEE_ZIP  zip_code              
,    c.INJURY_ZIP5   Injury_zip  
,    c.INJURY_DATE                        
,    cl.BIRTH_DATE DATE_OF_BIRTH          
,    c.PERIODIC_ROLL_IND current_roll_type  
--,    c.CASE_STATUS                      
,    c.INJURY_NATURE_CODE_DESCR                        
,    c.ACCEPTED_COND primary_accepted_condition    
,    cz.CURRENT_ZIP    
 
FROM  IDRDATA.DIM_HC_CASE_DETAIL c
,     IDRDATA.DIM_HC_CLAIMANT_DETAIL cl
,     IDRDATA.DIM_HC_COMPENSATION_DETAIL comp
,     IDRDATA.DIM_MT_ZIP5 Z5  
,     IDRDATA.DIM_MT_ZIP9 Z9
--  
-- current zip - use PAYEE_ZIP unless it is null or EFT is being used. In those cases use ZIP from Claimant table)
--
,    (SELECT   c.CASE_NUMBER,  
        case when comp.EFT_ACCOUNT_TYPE IN ('C','S') then 'EFT'
             else 'OTHERS' end EFTorNOT,
        DECODE( comp.PAYEE_ZIP, NULL, SUBSTR(cl.ZIP,1,5),                  
                comp.PAYEE_ZIP) current_zip,
        STATE, comp.GROSS_PAYMENT
        FROM    IDRDATA.DIM_HC_COMPENSATION_DETAIL comp,
                IDRDATA.DIM_HC_CLAIMANT_DETAIL     cl,
                IDRDATA.DIM_HC_CASE_DETAIL         c
        WHERE    c.CASE_NUMBER = comp.CASE_NUMBER
          AND    c.CASE_NUMBER = cl.CASE_NUMBER
          AND    cl.CASE_NUMBER = comp.CASE_NUMBER
          AND    comp.Payee_relationship_code = 'CL'
--            AND     SUBSTR(c.CASE_NUMBER,1,5) = '01206'
          AND    c.EXPIRATION_DATE IS NULL  
          AND    cl.EXPIRATION_DATE IS NULL
          AND    comp.PAYMENT_DATE > '01 DEC 2012'
          AND    comp.GROSS_PAYMENT > 0
          AND    c.PERIODIC_ROLL_IND = 'P'
        ) cz  

-- Date range
,    (SELECT max(PAYMENT_DATE) max_date,
            (max(PAYMENT_DATE)-28) min_date
        FROM    IDRDATA.DIM_HC_COMPENSATION_DETAIL) md



-- Pick up employee status and termination date, if available
,  (SELECT cl.LAST_NAME,cl.CASE_NUMBER,cl.SSN, e.status, e.TERMINATION_DATE
    FROM      IDRDATA.DIM_HC_CLAIMANT_DETAIL cl
    LEFT JOIN IDRDATA.DIM_EMPLOYEE e
        on         cl.SSN = e.SSN
        AND        cl.EXPIRATION_DATE IS NULL
        AND        e.EXPIRATION_DATE IS NULL) emp


--Outer WHERE
WHERE      c.CASE_NUMBER = comp.CASE_NUMBER(+)  
AND        c.CASE_NUMBER = cz.CASE_NUMBER
AND        c.CASE_NUMBER = cl.CASE_NUMBER
AND        old_info.CASE_NUMBER = c.CASE_NUMBER  
AND        cz.CURRENT_ZIP = Z5.ZIP5(+)
AND        (cz.current_zip || '9998') = z9.ZIP9
--AND        cl.CASE_NUMBER = comp.CASE_NUMBER
AND        c.EXPIRATION_DATE IS NULL
AND        cl.EXPIRATION_DATE IS NULL
AND        z5.EXPIRATION_DATE IS NULL    
AND        cz.STATE <> old_info.injury_state              
AND        comp.Payee_relationship_code = 'CL'
AND        comp.GROSS_PAYMENT > 0
AND        comp.PAY_TYPE IN ('1')
AND        comp.PAYMENT_DATE between md.min_date and md.max_date
AND        c.PERIODIC_ROLL_IND = 'P'
0
Comment
Question by:ktylerconk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 200 total points
ID: 38844605
you're doing a lot more when you embed it in the second, larger query

also, you have no join conditions between your EMP inline view and the rest of the tables
so, if your EMP query returns 100 rows and the rest of the query returns 1000 rows, you're generating 100,000 rows
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38844618
you also have a syntax error in your query

 CASE
           WHEN   (TO_CHAR(emp.TERMINATION_DATE, 'Mon DD, YYYY') IS NULL THEN 'Date Unavailable'
           ELSE TO_CHAR(emp.TERMINATION_DATE, Mon DD, YYYY')
     END Termination_dt          


missing quote and mismatched parentheses
0
 

Author Closing Comment

by:ktylerconk
ID: 38905186
Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question