Alternative way to left join in SQL ORACLE DB -
if run query below query:
select day.m_cmp_typo m_cmp_typo, day.m_sptcv m_sptcv, day.m_cnt_vs2 m_cnt_vs2, day.m_cnt_org m_cnt_org, day.m_pl_cgr2 m_pl_cgr2, day.m_pl_cgu2 m_pl_cgu2, day.m_pl_csfi2 m_pl_csfi2, day.m_pl_ftfi2 m_pl_ftfi2, day.m_pl_rvr2 m_pl_rvr2, day.m_pl_rvu2 m_pl_rvu2, day.m_trn_fmly m_trn_fmly, day.m_trn_grp m_trn_grp, day.m_trn_type m_trn_type, day.m_cnt_vs2 m_cnt_vs2, day.m_c_cur_pl m_c_cur_pl, day.m_instrlabel m_instrlabel, day.m_nb m_nb, day.m_pl_inscur m_pl_inscur, day.m_tp_cntrplb m_tp_cntrplb, day.m_tp_pfolio m_tp_pfolio, day.m_eco_pl m_eco_pl, day.m_cnt_id m_cnt_id, day.m_eco_pl_usd m_eco_pl_usd, day.m_pos_curr2 m_pos_curr2, day.m_curr2 m_curr2, day.m_tp_qtyeq m_tp_qtyeq, day.m_tp_uqtyeq m_tp_uqtyeq, day.m_tp_lqty32 m_tp_lqty32, day.m_tp_uqty m_tp_uqty, --day.m_eco_pl - daily.m_eco_pl daily_var, --day.m_eco_pl - month.m_eco_pl mtd, day.m_eco_pl - year.m_eco_pl ytd, --day.m_sptcv * (day.m_eco_pl - daily.m_eco_pl) daily_var_usd, --day.m_sptcv * (day.m_eco_pl - month.m_eco_pl) mtd_usd, day.m_sptcv * (day.m_eco_pl - year.m_eco_pl) ytd_usd rt_plvar_rep day left join rt_plvar_rep year on day.m_nb = year.m_nb --left join rt_plvar_rep month on day.m_nb = month.m_nb --left join rt_plvar_rep daily on day.m_nb = daily.m_nb day.m_ref_data = 18 , year.m_ref_data = 20 --and month.m_ref_data = 0 --and daily.m_ref_data = 0 it returns expected behavior, means returns 27 rows knowing year.m_ref_data = 20 exists in db. else if run 1 have no values. since m_ref_data=0 doesn't exist in table expecting query returns 27 rows related columns should return null, that's not case.
i have tried replace , didn't work either. returned 1728 rows wrong answer, should return 11. question why left join isn't working expecting ?
select day.m_cmp_typo m_cmp_typo, day.m_sptcv m_sptcv, day.m_cnt_vs2 m_cnt_vs2, day.m_cnt_org m_cnt_org, day.m_pl_cgr2 m_pl_cgr2, day.m_pl_cgu2 m_pl_cgu2, day.m_pl_csfi2 m_pl_csfi2, day.m_pl_ftfi2 m_pl_ftfi2, day.m_pl_rvr2 m_pl_rvr2, day.m_pl_rvu2 m_pl_rvu2, day.m_trn_fmly m_trn_fmly, day.m_trn_grp m_trn_grp, day.m_trn_type m_trn_type, day.m_cnt_vs2 m_cnt_vs2, day.m_c_cur_pl m_c_cur_pl, day.m_instrlabel m_instrlabel, day.m_nb m_nb, day.m_pl_inscur m_pl_inscur, day.m_tp_cntrplb m_tp_cntrplb, day.m_tp_pfolio m_tp_pfolio, day.m_eco_pl m_eco_pl, day.m_cnt_id m_cnt_id, day.m_eco_pl_usd m_eco_pl_usd, day.m_pos_curr2 m_pos_curr2, day.m_curr2 m_curr2, day.m_tp_qtyeq m_tp_qtyeq, day.m_tp_uqtyeq m_tp_uqtyeq, day.m_tp_lqty32 m_tp_lqty32, day.m_tp_uqty m_tp_uqty, day.m_eco_pl - daily.m_eco_pl daily_var, day.m_eco_pl - month.m_eco_pl mtd, day.m_eco_pl - year.m_eco_pl ytd, day.m_sptcv * (day.m_eco_pl - daily.m_eco_pl) daily_var_usd, day.m_sptcv * (day.m_eco_pl - month.m_eco_pl) mtd_usd, day.m_sptcv * (day.m_eco_pl - year.m_eco_pl) ytd_usd rt_plvar_rep day left join rt_plvar_rep year on day.m_nb = year.m_nb left join rt_plvar_rep month on day.m_nb = month.m_nb left join rt_plvar_rep daily on day.m_nb = daily.m_nb day.m_ref_data = 18 , year.m_ref_data = 0 , month.m_ref_data = 0 , daily.m_ref_data = 0
this from , where clauses first query:
from rt_plvar_rep day left join rt_plvar_rep year on day.m_nb = year.m_nb day.m_ref_data = 18 , year.m_ref_data = 20 the where clause turns left join inner join. value of year.m_ref_data null, fails condition.
for left join, conditions should go in on clause:
from rt_plvar_rep day left join rt_plvar_rep year on day.m_nb = year.m_nb , year.m_ref_data = 20 day.m_ref_data = 18 conditions on first table should stay in where.
Comments
Post a Comment