Below is the join i perform that i do with NBI_DIM_OBS table :
left outer join prj_obs_associations poa on res.id=poa.record_id and poa.table_name='SRM_RESOURCES'join nbi_dim_obs obs on poa.unit_id=obs.obs_unit_idjoin prj_obs_types pot on pot.id=obs.obs_type_id
but i seems to be missing some rows that is being returned. Also, I see the issue was caused because of the NBI_DIM_OBS table join. What could cause rows missing is the NBI_DIM_OBS table when joined with resource table?
NOTE: I'm aware of the fact that it's data extraction job that updates this data extract table in Clarity. I tried running the job and still missing rows in nbi_dim_obs table.
Please consider making it a LEFT JOIN. If the Resource hasn't been allocated to an OBS, they won't appear as your current SQL is a JOIN (aka you need to be on an OBS).
I haven't fully analysised your SQL, but above should assist you in trouble shooting your SQL further.
I tried with the LEFT JOIN and the result set is still the same number. Also, there seems to be missing resources with the data returned.
select res.* from
bring back missing resources? Stupid question but making sure
left outer join prj_obs_associations poa on res.id=poa.record_id and poa.table_name='SRM_RESOURCES'
bring back missing resources?
left outer join prj_obs_associations poa on res.id=poa.record_id and poa.table_name='SRM_RESOURCES'left join nbi_dim_obs obs on poa.unit_id=obs.obs_unit_idleft join prj_obs_types pot on pot.id=obs.obs_type_id
bring back missing resources? All three should.
Perhaps it is your where clause, which you have not provided where the issue is. For example, if your where clause has pot.name = 'Department' (as an example), then the left join won't make any differences.
Why are you even using the NBI_tables ; does the query return data when you use prj_obs_units / prj_obs_types instead?
Hi Dave_3.0, I'm reading each and every separate unit and thus using NBI table.