roland.parrotte

Non-Sequential IDs in Clarity

Discussion created by roland.parrotte Champion on Jan 11, 2018

Majority of objects in Clarity have an unique ID - for example, inv_investments.id, odf_ca_cop_prj_statusrpt.id.  It is a unique field that Clarity creates (generally refereed to as the 5000000 number as all the separate ID's in Clarity will start with 5000000).

 

I recently had build logic using this ID on a custom sub-object on Project.  It wasn't the Status Report (cop_prj_statusrpt) but as most of you are used to this object, I will use this as the example.  To determine the first Status Report created on the Project, I was using min(id), and to find the latest Status Report created on the Project, I was using max(id)  Something along the line:

select inv.code,
rfirst.name first_report_name,
rlast.name last_report_name
from inv_investments inv
left join odf_ca_cop_prj_statusrpt rfirst on rfirst.odf_parent_id = inv.id and rfirst.id = (select min(id) from odf_ca_cop_prj_statusrpt where odf_parent_id = inv.id)
left join odf_ca_cop_prj_statusrpt rlast on rlast.odf_parent_id = inv.id and rlast.id = (select max(id) from odf_ca_cop_prj_statusrpt where odf_parent_id = inv.id)

 

This was all working in our initial tests in DEV, but when migrated to PROD, and after 2 months use, we discovered that the ID's were not sequentially allocated.

 

As an example from our PROD environment:

select id, created_date from inv_investments
order by created_date desc
Returns
5,014,008 2018-01-04 15:06:52.0
5,013,009 2017-12-18 14:18:41.0
5,013,008 2017-12-12 18:10:52.0
5,013,007 2017-12-12 18:06:43.0
5,013,006 2017-12-12 17:33:26.0
5,013,005 2017-12-12 16:30:52.0
5,014,007 2017-12-12 14:19:19.0  -- OUT OF ORDER
5,013,004 2017-12-12 13:57:26.0
5,014,006 2017-12-11 18:47:18.0

 

This is potentially due to the SaaS environments using Oracle RAC and if the id creation is using NEXTVAL, then each Oracle Server may have different caches for NEXTVAL (just a guess).

 

In the above case, the code was modified to use RANK() OVER (PARTITION BY odf_parent_id ORDER BY created_date DESC ) RANK and search where RANK = 1 (which will return the last record created, and to find the first one, sort created_date ASC).

 

Alternatively, I could have also created a new attribute, and have it auto-generated, which will be sequential.  How Clarity auto-generated these numbers, I can't comment, but so far, I have not seen them being non-sequential like the ID's.

Outcomes