Leave a comment at the end of this page or email contact@krishagni.com
Populate the correct specimen type id in specimen requirement and specimen table.
Reason: When upgrading from v6.1 to v6.3, there was a bug in migration script, which was populating the specimen_type for ‘Not Specified’ dropdown values as ‘Not Specified - <Class>’. The actual dropdown value was ‘<Class> - Not Specified’. Due to this, in specimen requirement and specimen table, NULL specimen_type_id was populated. The issue is fixed in 7.0 builds.
Specimen Requirements
Check NULL specimen_type_id present in the specimen requirement table.
select
count(*)
from
catissue_cp_req_specimen
where
(specimen_class_id is null or specimen_type_id is null) and
activity_status != 'Disabled';
Step 1: Take a backup of the existing specimen requirement table.
create table cat_cp_req_specimen_bk as select * from catissue_cp_req_specimen;
Step 2: Note down the dropdown IDs for each specimen class/type.
Query 1: select identifier from catissue_permissible_value where value='<specimen_type>';
Query 2: select identifier from catissue_permissible_value where value='<specimen_class>';
Example:
Specimen Class: Cell and Specimen Type: Cell - Not Specified
select
identifier
from
catissue_permissible_value
where
value='Cell - Not Specified';
select
identifier
from
catissue_permissible_value
where
value = 'Cell';
Step 3: Update specimen_type_id for each specimen class/type in specimen requirement table.
For Cell:
update
catissue_cp_req_specimen
set
specimen_type_id = <output identifier of query 1>
where
specimen_type = 'Not Specified - Cell' and
activity_status != 'Disabled' and
specimen_type_id is null and
specimen_class_id = <output identifier of query 2>;
For Molecular:
update
catissue_cp_req_specimen
set
specimen_type_id=<output of query 1>
where
specimen_type = 'Not Specified - Molecular' and
activity_status != 'Disabled' and
specimen_type_id is null and
specimen_class_id = <output identifier of query 2>;
For Tissue:
update
catissue_cp_req_specimen
set
specimen_type_id = <output identifier of query 1>
where
specimen_type = 'Not Specified - Tissue' and
activity_status != 'Disabled' and
specimen_type_id is null and
specimen_class_id = <output identifier of query 2>;
For Fluid:
update
catissue_cp_req_specimen
set
specimen_type_id=21
where
specimen_type = 'Not Specified - Fluid' and
activity_status != 'Disabled' and
specimen_type_id is null and
specimen_class_id = <output identifier of query 2>;
Run the first query again to check if any records are present with specimen_type_id = NULL.
Specimen
Check NULL specimen_type_id in the specimen table.
select
specimen_type, count(*)
from
catissue_specimen
where
specimen_type_id is null
group by
specimen_type;
Step 1: Backup the existing specimen table.
create table catissue_specimen_bk as select * from catissue_specimen;
Step 2: Update the specimen table.
For Cell:
update
catissue_specimen
set
specimen_type_id = (
select
identifier
from
catissue_permissible_value
where
public_id = 'specimen_type'
and value = 'Cell - Not Specified'
)
where
specimen_class = 'Cell'
and specimen_type = 'Not Specified - Cell';
For Molecular:
update
catissue_specimen
set
specimen_type_id = (
select
identifier
from
catissue_permissible_value
where
public_id = 'specimen_type'
and value = 'Molecular - Not Specified'
)
where
specimen_class = 'Molecular'
and specimen_type = 'Not Specified - Molecular';
For Tissue:
update
catissue_specimen
set
specimen_type_id = (
select
identifier
from
catissue_permissible_value
where
public_id = 'specimen_type' and
value = 'Tissue - Not Specified'
)
where
specimen_class = 'Tissue' and
specimen_type = 'Not Specified - Tissue';
For Fluid:
update
catissue_specimen
set
specimen_type_id = (
select
identifier
from
catissue_permissible_value
where
public_id = 'specimen_type'
and value = 'Fluid - Not Specified'
)
where
specimen_class = 'Fluid'
and specimen_type = 'Not Specified - Fluid';
Run the initial query from the specimen section to make sure there is no specimen_type_id with a NULL value.
Leave a comment at the end of this page or email contact@krishagni.com