View source for Clinical trials curation

This is the curator page for clinical trials curation. 

It would be a good idea to update drugbank and HMDB and their synonyms before updating CT.

= curating subname, the basis for joining CT to substance =
These are examples. look closely before executing.
 update subname set name=regexp_replace(name, ' *.inn.*$','') where name like '%[inn%';
 begin; delete from subname where length(name) < 10 and name like '% %';
 begin; delete from subname where length(name) < 7 and name like '9%'; 1-9
# update after editing subname table
 begin; update subname set terms=to_tsvector('english',name),query=plainto_tsquery(name);
# to solve the no lexemees problem
 select * from subname where numnode(plainto_tsquery(name))=0 limit 10;

= create the mapping table between them = 
Do this after updating subname
 insert into clinical1.ct2subint (sub_id_fk, ct2int_fk)
 select distinct sub_id_fk, ct2int_id from ctinttemp as ct
 join clinical1.ct2 on ct.code=ct_code
 join clinical1.ct2int as cti on ct2_id=cti.ct2_fk and cti.name=ct.name
 join subname on ct.terms@@subname.query
 where not exists(select 1 from clinical1.ct2subint as e where e.sub_id_fk=subname.sub_id_fk and e.ct2int_fk=cti.ct2int_id);

= History =
Some history about how the tables were originally created. 
[[Creating clinical name mappings]].  Note that this page should be obsoleted by the current page.


[[Category:Curator]]