Answered by:
Remove Sub Query

-
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
Question
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 CotterMVP Friday, April 21, 2017 6:32 PM
- Marked as answer by sdangelo Friday, April 21, 2017 7:53 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
- Edited by Hilary CotterMVP Friday, April 21, 2017 6:14 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 -
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 CotterMVP Friday, April 21, 2017 6:32 PM
- Marked as answer by sdangelo Friday, April 21, 2017 7:53 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 <> HVHZor
Obtain these records
• Dwelling built after to 2001
• Opening Protection
• wind mit credits applied
• Florida Policesand then remove
• YR Built < 2012
• Terrain <> HVHZSandy
-