Solved

Why does embedded SELECT cause

Posted on 2013-02-01
3
314 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
  • 2
3 Comments
 
LVL 73

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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

864 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now