Page Comparison

Pre-requisite: User needs to have access to the database to execute the SQL query

Steps:

Import the workflow for at least one CP from the user interface. Image RemovedQuery for collection protocol identifiers for which the workflow is to be set from the database table catissue_collection_protocol.using UI as below.Image Added

Below SQL sets the workflow set for CP using UI (whose ID is 1) to other CPs (CP1, CP2, CP3, CP4)

select IDENTIFIER from

Code Block

language	sql
title	get_CP_ID

	MySQL

update 
  os_cp_workflows dst 
  inner join os_cp_workflows src on src.cp_id = 1 
set 
  dst.workflows = src.workflows 
where 
  dst.cp_id in (
    select
      identifier 
    from 
      catissue_collection_protocol 
    where SHORT_TITLE 
      short_title in ('CP1', 'CP2', 'CP3', 'CP4');
>>31 32
34
37

Use the output of the above SQL for the below SQL

Below SQL sets the workflow set for 'AB Lung Cancer Trial' CP to the CPs with identifiers 31, 32, 34 and 37

);

update

Code Block

language	sql
title	cp_workflow_update

Oracle

merge into
  os_cp_workflows a inner join dst
using (
  select
    src.cp_id, src.workflows
  from
    os_cp_workflows src
b) on b(src.cp_id = 1)
when matched then
  update 
    set adst.workflows = bsrc.workflows 
  where 
    a.CP_IDdst.cp_id in (
      select
        identifier 
      from 
        catissue_collection_protocol 
      where 
        short_title in (31,32,34,37);'CP1', 'CP2', 'CP3', 'CP4')
    )

Versions Compared

Old Version 2

New Version 3

Key