Clarity

 View Only
  • 1.  Issue with NBI_DIM_OBS Table

    Posted Nov 05, 2018 05:00 PM

    Below is the join i perform that i do with NBI_DIM_OBS table :

     

    srm_Resources res

    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_id
    join 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. 



  • 2.  Re: Issue with NBI_DIM_OBS Table

    Posted Nov 05, 2018 10:54 PM

    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.



  • 3.  Re: Issue with NBI_DIM_OBS Table

    Posted Nov 07, 2018 02:28 PM

    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.



  • 4.  Re: Issue with NBI_DIM_OBS Table
    Best Answer

    Posted Nov 07, 2018 09:26 PM

    Does

    select res.* from

    srm_Resources res

    bring back missing resources?  Stupid question but making sure

     

    Does

    select res.* from

    srm_Resources res

    left outer join prj_obs_associations poa on res.id=poa.record_id and poa.table_name='SRM_RESOURCES'

    bring back missing resources?

     

    Does

    select res.* from

    srm_Resources res

    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_id
    left 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.



  • 5.  Re: Issue with NBI_DIM_OBS Table

    Posted Nov 08, 2018 03:41 AM

    Why are you even using the NBI_tables ; does the query return data when you use prj_obs_units / prj_obs_types instead?



  • 6.  Re: Issue with NBI_DIM_OBS Table

    Posted Nov 08, 2018 09:05 AM

    Hi Dave_3.0, I'm reading each and every separate unit and thus using NBI table.