i'm trying build query in toad data point. have subquery has row number identify records i'm interested in. subquery needs left joined onto main table when row number 1. here's query i'm trying visualize:
select distinct e.emplid, e.acad_career ps_stdnt_enrl e left join ( select acad_career, row_number() on (partition acad_career order effdt desc) rn ps_acad_car_tbl ) t on t.acad_career = e.acad_career , rn = 1 when try replicate this, row number condition placed in global clause. not intended functionality because removes records don't have match in subquery making inner join.
here query it's generating:
select distinct e.emplid, e.acad_career, t.rn sysadm.ps_stdnt_enrl e left outer join (select ps_acad_car_tbl.acad_career, row_number () on (partition acad_career order effdt desc) rn sysadm.ps_acad_car_tbl ps_acad_car_tbl) t on (e.acad_career = t.acad_career) (t.rn = 1) is there way query builder place row number condition on left join instead of global clause?
i found way work.
- add calculated field main table value of 1.
- join row number new calculated field.
now query has filter in join condition instead of clause joins intended. here query made:
select distinct e.emplid, e.acad_career, t.rn sysadm.ps_stdnt_enrl e left outer join (select ps_acad_car_tbl.acad_career, row_number () on (partition acad_career order effdt desc) rn sysadm.ps_acad_car_tbl ps_acad_car_tbl) t on (e.acad_career = t.acad_career) , (1 = t.rn) 