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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

this was still considered a SubQuery

---

Sandy

Answer 5

This is the result I wanted but it is still written as sub query

---

Sandy

Answer 6

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

Answer 7

Thanks for that information on how a join turns into a inner join

---

Sandy