Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Why does embedded SELECT cause

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
ktylerconk
Asked:
ktylerconk
  • 2
1 Solution
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
ktylerconkAuthor Commented:
Thanks!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now