Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Why does embedded SELECT cause

Posted on 2013-02-01
3
Medium Priority
?
320 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 800 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

688 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