Solved

Why does embedded SELECT cause

Posted on 2013-02-01
3
319 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

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 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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

630 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