E-Notebook Enterprise: SQL query to get all open experiments

If you need to find all open experiments at your Enotebook application

select  

c.Section_set_key Collection_key,

c.name Collection_name ,

ct.collection_type_name collection_type_name,

p.username  Collection_Owner,

s.name Collection_State ,

v.action_date  created_on

from eln_collections c ,

   eln_collection_types ct,

   eln_people p ,

   eln_states s ,

   ena_versions v

Where

c.collection_type_key = ct.section_set_key

And (

  upper(ct.collection_type_name) like '%NOTEBOOK%'

  or upper(ct.collection_type_name) like '%EXPERIMENT%'

)

And c.state_key = s.primary_key

And upper(s.name) = 'OPEN'

And p.primary_key = c.owner_key

And s.collection_type_key = ct.section_set_key

And v.primary_key =  c.creation_version

AND v.action_date BETWEEN TO_DATE('01/09/2023', 'DD/MM/YYYY') AND TO_DATE('30/09/2025', 'DD/MM/YYYY')

AND c.section_set_key <> c.collection_type_key

And c.is_active = 1

Notes: 1. The date range in the query (v.action_date BETWEEN...) can be modified to filter results for specific time periods. 2. For large databases, it's recommended to use date ranges to limit the number of records returned.