none
Remove Sub Query

    Question

  • I need to remove the subquery from this code below.
    SELECT 
    
      'Construction Date and Opening Protection'                   AS Reason
      
      ,TRANS.TRANS_CTGRY                    AS TRANS_CTGRY
      ,TRANS.TRANS_TYP                      AS TRANS_TYP
      ,TRANS.TRANS_ID                       AS TRANS_ID
      ,POLICY.POLICY_TRM_ID                 AS POLICY_TRM_ID
      ,TO_DATE(TO_CHAR(TRANS.TRANS_DT_TM, 'YYYY-MM-DD'), 'YYYY-MM-DD') AS TRANS_DATE
      ,POLICY.STATE_DESC                    AS STATE_DESC
      ,POLICY.POLICY_NO                     AS POLICY_NO
      ,POLICY.POLICY_RNWL_NO                AS POLICY_RNWL_NO 
      ,POLICY.FORM_CODE                     AS FORM_CODE
      ,POLICY.BEGN_DT                       AS BEGIN_DATE 
      ,POLICY.END_DT                        AS END_DATE 
      ,POLICY.STS_DESC                      AS STS_DESC
      ,POLICY.SUB_BRKR                      AS SUB_BRKR
      
      ,PLOC.YR_BLT                          AS YR_BLT
      ,STRUCT.OPNNG_PRTCTN                  AS OPNNG_PRTCTN
      ,POLICY_CVRG_DTLS.CVRG_TYP            AS CVRG_TYP
      
      ,STRUCT.TRRN                          AS TRRN
      
       --Policy Premium
      ,POLICY_CVRG_DTLS.ANNL_PREM           AS ANNL_PREM
      ,POLICY_CVRG_DTLS.TRM_PREM            AS TRM_PREM
      
      FROM REP_TRANS_POLICY.TRANS                   TRANS 
    LEFT JOIN REP_TRANS_POLICY.POLICY               POLICY              ON (POLICY.TRANS_ID = TRANS.TRANS_ID AND POLICY.SSHOT_EFF_TSTMP = TRANS.TRANS_DT_TM AND 
                                                                            TRANS.TRANS_DT_TM < POLICY.SSHOT_EXPRY_TSTMP)
    Left Join REP_TRANS_POLICY.PLOC_N_SUBCONTR      PLOC                ON (PLOC.TRANS_ID   = TRANS.TRANS_ID AND PLOC.POLICY_TRM_ID   = POLICY.POLICY_TRM_ID)
    Left Join  REP_TRANS_POLICY.POLICY_STRUCT       STRUCT              ON (STRUCT.TRANS_ID = TRANS.TRANS_ID)
    LEFT JOIN REP_TRANS_POLICY.POLICY_CVRG_DTLS     POLICY_CVRG_DTLS    ON (POLICY_CVRG_DTLS.TRANS_ID = TRANS.TRANS_ID AND POLICY_CVRG_DTLS.POLICY_TRM_ID = POLICY.POLICY_TRM_ID)
    WHERE 
    
        TRANS.TRANS_CTGRY in ('POLICY') 
    AND TRANS.TRANS_TYP   in ('ISSUANCE')
    AND UWG_CODE          in ('NB')
          
    AND POLICY.LINE  in ('HO', 'DF')
    
    AND POLICY.STATE_DESC = 'FL'
    
    --Dwelling Built after 2001
    AND PLOC.YR_BLT > 2001
    --Opening Protection
    AND STRUCT.OPNNG_PRTCTN in ('H', 'BS')
    --Wind Mititageion Credit
    AND POLICY_CVRG_DTLS.CVRG_TYP       = 'ST_WLM'
    
    AND POLICY.POLICY_NO not in (
    SELECT PLOC.POLICY_NO
    FROM  REP_TRANS_POLICY.PLOC_N_SUBCONTR          PLOC               
    Left Join  REP_TRANS_POLICY.POLICY_STRUCT       STRUCT              ON (STRUCT.TRANS_ID = PLOC.TRANS_ID)
    WHERE 
        PLOC.YR_BLT >=  2012
    AND STRUCT.TRRN  IN ('HVHZ'))
    
    --AND POLICY.POLICY_NO = 'HOH302815'
       
    ORDER BY POLICY.POLICY_NO 
            ,POLICY_RNWL_NO


    Sandy

    Friday, April 21, 2017 6:07 PM

Answers

  • Stop fooling yourself. Get rid of the left joins - your where clause turns them into inner joins. That applies both to the main query and to the subquery.
    • Proposed as answer by Hilary Cotter Friday, April 21, 2017 6:32 PM
    • Marked as answer by sdangelo Friday, April 21, 2017 7:53 PM
    Friday, April 21, 2017 6:28 PM

All replies

  • This is an Oracle query. You should post it to an Oracle forum.

    This might be what you are looking for though:

    SELECT 
    
      'Construction Date and Opening Protection'                   AS Reason
      
      ,TRANS.TRANS_CTGRY                    AS TRANS_CTGRY
      ,TRANS.TRANS_TYP                      AS TRANS_TYP
      ,TRANS.TRANS_ID                       AS TRANS_ID
      ,POLICY.POLICY_TRM_ID                 AS POLICY_TRM_ID
      ,TO_DATE(TO_CHAR(TRANS.TRANS_DT_TM, 'YYYY-MM-DD'), 'YYYY-MM-DD') AS TRANS_DATE
      ,POLICY.STATE_DESC                    AS STATE_DESC
      ,POLICY.POLICY_NO                     AS POLICY_NO
      ,POLICY.POLICY_RNWL_NO                AS POLICY_RNWL_NO 
      ,POLICY.FORM_CODE                     AS FORM_CODE
      ,POLICY.BEGN_DT                       AS BEGIN_DATE 
      ,POLICY.END_DT                        AS END_DATE 
      ,POLICY.STS_DESC                      AS STS_DESC
      ,POLICY.SUB_BRKR                      AS SUB_BRKR
      
      ,PLOC.YR_BLT                          AS YR_BLT
      ,STRUCT.OPNNG_PRTCTN                  AS OPNNG_PRTCTN
      ,POLICY_CVRG_DTLS.CVRG_TYP            AS CVRG_TYP
      
      ,STRUCT.TRRN                          AS TRRN
      
       --Policy Premium
      ,POLICY_CVRG_DTLS.ANNL_PREM           AS ANNL_PREM
      ,POLICY_CVRG_DTLS.TRM_PREM            AS TRM_PREM
      
      FROM REP_TRANS_POLICY.TRANS                   TRANS 
    LEFT JOIN REP_TRANS_POLICY.POLICY               POLICY              ON (POLICY.TRANS_ID = TRANS.TRANS_ID AND POLICY.SSHOT_EFF_TSTMP = TRANS.TRANS_DT_TM AND 
                                                                            TRANS.TRANS_DT_TM < POLICY.SSHOT_EXPRY_TSTMP)
    Left Join REP_TRANS_POLICY.PLOC_N_SUBCONTR      PLOC                ON (PLOC.TRANS_ID   = TRANS.TRANS_ID AND PLOC.POLICY_TRM_ID   = POLICY.POLICY_TRM_ID)
    Left Join  REP_TRANS_POLICY.POLICY_STRUCT       STRUCT              ON (STRUCT.TRANS_ID = TRANS.TRANS_ID)
    LEFT JOIN REP_TRANS_POLICY.POLICY_CVRG_DTLS     POLICY_CVRG_DTLS    ON (POLICY_CVRG_DTLS.TRANS_ID = TRANS.TRANS_ID AND POLICY_CVRG_DTLS.POLICY_TRM_ID = POLICY.POLICY_TRM_ID)
    left join (SELECT PLOC.POLICY_NO
    FROM  REP_TRANS_POLICY.PLOC_N_SUBCONTR PLOC               
    Left Join  REP_TRANS_POLICY.POLICY_STRUCT       STRUCT              
    ON (STRUCT.TRANS_ID = PLOC.TRANS_ID)
    WHERE 
        PLOC.YR_BLT >=  2012
    AND STRUCT.TRRN  IN ('HVHZ')) as X on X.POLICY_NO=
     POLICY.POLICY_NO 
    WHERE 
    
        TRANS.TRANS_CTGRY in ('POLICY') 
    AND TRANS.TRANS_TYP   in ('ISSUANCE')
    AND UWG_CODE          in ('NB')
          
    AND POLICY.LINE  in ('HO', 'DF')
    
    AND POLICY.STATE_DESC = 'FL'
    
    --Dwelling Built after 2001
    AND PLOC.YR_BLT > 2001
    --Opening Protection
    AND STRUCT.OPNNG_PRTCTN in ('H', 'BS')
    --Wind Mititageion Credit
    AND POLICY_CVRG_DTLS.CVRG_TYP       = 'ST_WLM'
    
    AND X.POLICY_NO is null
    
    --AND POLICY.POLICY_NO = 'HOH302815'
       
    ORDER BY POLICY.POLICY_NO 
            ,POLICY_RNWL_NO

    Friday, April 21, 2017 6:13 PM
  • Good call, Hilary! I didn't notice at first.

    In any case, you should also explain why you want to remove subquery and provide more information about this complex query. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, April 21, 2017 6:15 PM
    Moderator
  • Stop fooling yourself. Get rid of the left joins - your where clause turns them into inner joins. That applies both to the main query and to the subquery.
    • Proposed as answer by Hilary Cotter Friday, April 21, 2017 6:32 PM
    • Marked as answer by sdangelo Friday, April 21, 2017 7:53 PM
    Friday, April 21, 2017 6:28 PM
  • this was still considered a SubQuery

    Sandy

    Friday, April 21, 2017 7:25 PM
  • This is the result I wanted but it is still written as sub query

    Sandy

    Friday, April 21, 2017 7:26 PM
  • Thanks for the information. I was looking at the business rules and I could read it two different ways.

    • Dwelling built after to 2001
    • Opening Protection
    • wind mit credits applied
    • Florida Polices             

    • YR Built < 2012
    • Terrain <> HVHZ    

    or  

    Obtain these records    

    • Dwelling built after to 2001
    • Opening Protection
    • wind mit credits applied
    • Florida Polices    

    and then remove  

    • YR Built < 2012
    • Terrain <> HVHZ                                                                                                  

                                                                                                        


    Sandy

    Friday, April 21, 2017 7:50 PM
  • Thanks for that information on how a join turns into a inner join

    Sandy

    Friday, April 21, 2017 7:53 PM