CLUSTER pub2.TERM;

[ Date: 2024-06-27 02:18:38 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:28:10 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:28:10 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:28:10 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:28:10 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:28:10 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:31:43 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:31:43 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:31:43 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:31:43 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:31:43 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:31:43 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:31:44 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:31:44 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:31:44 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:31:44 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:32:33 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:32:33 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:32:33 ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 02:32:33 ]

select pub2.maint_term_derive_data ();

[ Date: 2024-06-27 05:02:55 - Bind query: yes ]

select pub2.maint_gene_chem_ref_gene_form_refresh ();

[ Date: 2024-06-27 01:12:24 - Bind query: yes ]

SELECT maint_term_derive_nm_fts ();

[ Date: 2024-06-27 02:14:31 - Bind query: yes ]

VACUUM FULL ANALYZE;

[ Date: 2024-06-27 03:06:26 - Bind query: yes ]

select pub2.maint_cached_value_refresh_data_metrics ();

[ Date: 2024-06-27 05:46:19 - Bind query: yes ]

SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;

[ Date: 2024-06-27 10:33:26 - Database: ctdprd51 - User: qaeu - Bind query: yes ]

/* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ */ SELECT maint_query_logs_archive ();

[ Date: 2024-06-27 00:16:26 ]

select pub2.maint_phenotype_term_derive_data ();

[ Date: 2024-06-27 05:12:46 - Bind query: yes ]

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1426816')) ORDER BY r.sort_txt LIMIT 50;

[ Date: 2024-06-27 15:00:08 - Database: ctdprd51 - User: qaeu - Bind query: yes ]

SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1241461') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;

[ Date: 2024-06-27 15:28:53 - Database: ctdprd51 - User: pubeu - Bind query: yes ]

INSERT INTO pub2.TERM_REFERENCE (term_id, object_type_id, reference_id, ixn_type_id) SELECT DISTINCT term_id, object_type_id, reference_id, ixn_type_id FROM ( SELECT gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE WHERE taxon_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub2.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub2.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub2.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub2.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT ee.exp_marker_term_id as term_id, ( SELECT object_type_id FROM term WHERE id = exp_marker_term_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_EVENT ee WHERE e.exp_event_id = ee.id AND exp_marker_term_id IS NOT NULL UNION SELECT er.term_id, ( SELECT object_type_id FROM term WHERE id = er.term_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_RECEPTOR er WHERE e.exp_receptor_id = er.id AND er.term_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM term WHERE id = chem_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_STRESSOR es WHERE e.exp_stressor_id = es.id AND chem_id IS NOT NULL UNION SELECT phenotype_id as term_id, ( SELECT object_type_id FROM term WHERE id = phenotype_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_OUTCOME eo WHERE e.exp_outcome_id = eo.id AND phenotype_id IS NOT NULL UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM term WHERE id = disease_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_OUTCOME eo WHERE e.exp_outcome_id = eo.id AND disease_id IS NOT NULL UNION SELECT phenotype_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = phenotype_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = term_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' AND taxon_id IS NOT NULL UNION SELECT from_gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = from_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT to_gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = to_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT from_taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = from_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT to_taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = to_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT DISTINCT t.id as taxon_id, t.object_type_id as taxon_object_id, r.id as reference_id, i.ixn_type_id as ixn_type_id FROM edit.REFERENCE_IXN ri, pub2.TERM t, edit.IXN i, pub2.REFERENCE r WHERE ri.taxon_acc_txt = t.acc_txt AND t.object_type_id = ( SELECT id FROM pub2.OBJECT_TYPE WHERE cd = 'taxon') AND ri.ixn_id = i.root_id AND i.ixn_type_id in ( SELECT id FROM edit.IXN_TYPE WHERE nm in ('CHEMICAL-DISEASE', 'GENE-DISEASE')) AND ri.reference_acc_txt = r.acc_txt AND ri.taxon_acc_txt IS NOT NULL AND ri.taxon_acc_txt <> '') as test UNION select ea.anatomy_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = 'EXPOSURE') as ixn_type_id from pub2.EXP_ANATOMY ea, pub2.EXP_OUTCOME eo, pub2.EXPOSURE e, pub2.REFERENCE r where ea.exp_outcome_id = eo.id and eo.id = e.exp_outcome_id and e.reference_acc_txt = r.acc_txt UNION select anatomy_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = 'PHENOTYPE') as ixn_type_id from pub2.IXN i, pub2.IXN_ANATOMY ia, edit.REFERENCE_IXN ri, pub2.REFERENCE r where i.id = ri.ixn_id and ri.reference_acc_txt = r.acc_txt and i.id = ia.ixn_id UNION select medium_term_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id, ( select id from ixn_type where nm = 'EXPOSURE') as ixn_type_id from pub2.EXP_EVENT ee, pub2.EXPOSURE e, pub2.REFERENCE r where ee.id = e.exp_event_id and e.reference_acc_txt = r.acc_txt and medium_term_id is not null;

[ Date: 2024-06-27 01:15:06 - Bind query: yes ]

SELECT maint_term_label_derive_nm_fts ();

[ Date: 2024-06-27 02:17:06 - Bind query: yes ]

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1426816')) ORDER BY r.sort_txt LIMIT 50;

[ Date: 2024-06-27 14:56:34 - Database: ctdprd51 - User: qaeu - Bind query: yes ]

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1426816')) ORDER BY r.sort_txt LIMIT 50;

[ Date: 2024-06-27 14:55:42 - Database: ctdprd51 - User: qaeu - Bind query: yes ]

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1426816')) ORDER BY r.sort_txt LIMIT 50;

[ Date: 2024-06-27 14:54:49 - Database: ctdprd51 - User: qaeu - Bind query: yes ]

SELECT /* PhenotypeDiseasesDAO */ phenotypeTerm.nm "GOName", phenotypeTerm.acc_txt "GOID", diseaseTerm.nm "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( SELECT STRING_AGG(DISTINCT chemTermNetwork.nm, '|')) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( SELECT STRING_AGG(DISTINCT geneTermNetwork.nm, '|')) "InferenceGeneSymbols" FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt on ptr.phenotype_id = pt.phenotype_id AND ptr.term_id = pt.term_id INNER JOIN term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'biological_process')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'biological_process')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'biological_process')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'biological_process')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'biological_process')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;

[ Date: 2024-06-27 11:39:45 - Database: ctdprd51 - User: qaeu - Bind query: yes ]

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1426816')) ORDER BY r.sort_txt LIMIT 50;

[ Date: 2024-06-27 14:54:06 - Bind query: yes ]

SELECT /* AllCuratedChemPhenoIxnsDAO */ chemTerm.nm ChemicalName, chemTerm.acc_txt ChemicalID, chemTerm.secondary_nm CasRN, phenoTerm.nm PhenotypeName, phenoTerm.acc_txt PhenotypeID, ( SELECT STRING_AGG(distinct coMentionTerm.nm || '^' || coMentionTerm.acc_txt || '^' || coMentionTerm.acc_db_cd, '|')) as coMentionedTerms, taxonTerm.nm Organism, taxonTerm.acc_txt OrganismID, i.ixn_prose_txt Interaction, i.actions_txt InteractionActions, ( SELECT STRING_AGG(distinct ia.level_seq + 1 || '^' || anatomyTerm.nm || '^' || anatomyTerm.acc_txt, '|' ORDER BY ia.level_seq + 1 || '^' || anatomyTerm.nm || '^' || anatomyTerm.acc_txt)) as anatomyTerms, STRING_AGG(distinct inferredTerm.nm || '^' || inferredTerm.acc_txt || '^' || inferredTerm.acc_db_cd, '|' ORDER BY inferredTerm.nm || '^' || inferredTerm.acc_txt || '^' || inferredTerm.acc_db_cd) InferenceGeneSymbols, STRING_AGG(distinct r.acc_txt, '|' ORDER BY r.acc_txt) PubMedIDs, ptr.ixn_id ignorecolumnIxnId FROM phenotype_term_reference ptr LEFT OUTER JOIN term taxonTerm ON ptr.taxon_id = taxonTerm.id INNER JOIN ixn i ON ptr.ixn_id = i.id INNER JOIN term phenoTerm ON ptr.phenotype_id = phenoTerm.id INNER JOIN reference r ON ptr.reference_id = r.id INNER JOIN term chemTerm ON ptr.term_id = chemTerm.id LEFT OUTER JOIN ixn_anatomy ia ON ptr.ixn_id = ia.ixn_id LEFT OUTER JOIN term anatomyTerm ON ia.anatomy_id = anatomyTerm.id LEFT OUTER JOIN phenotype_term_reference ptr2 ON ptr.ixn_id = ptr2.ixn_id and (ptr.term_id <> ptr2.term_id or ptr.phenotype_id <> ptr2.phenotype_id) LEFT OUTER JOIN phenotype_term_reference ptr3 ON ptr.term_id = ptr3.term_id and (ptr.phenotype_id = ptr3.phenotype_id and ptr3.source_cd = 'I') LEFT OUTER JOIN term coMentionTerm ON coMentionTerm.id = ptr2.term_id LEFT OUTER JOIN term inferredTerm ON inferredTerm.id = ptr3.via_term_id WHERE ptr.source_cd = 'C' AND ptr.term_object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'chem') GROUP BY chemTerm.nm, chemTerm.acc_txt, chemTerm.secondary_nm, phenoTerm.nm, phenoTerm.acc_txt, taxonTerm.nm, taxonTerm.acc_txt, i.ixn_prose_txt, i.actions_txt, ptr.ixn_id ORDER BY chemTerm.nm, phenoTerm.nm;

[ Date: 2024-06-27 11:36:57 - Database: ctdprd51 - User: qaeu - Bind query: yes ]

SELECT /* AllCDRelationsDAO */ c.nm "ChemicalName", c.acc_txt "ChemicalID", c.secondary_nm "CasRN", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", a.action_type_nm "DirectEvidence", g.nm "InferenceGeneSymbol", cdr.network_score "InferenceScore", STRING_AGG(cdr.source_acc_txt, '|' ORDER BY cdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM chem_disease_reference cdr INNER JOIN term d ON cdr.disease_id = d.id INNER JOIN term c ON cdr.chem_id = c.id LEFT OUTER JOIN chem_disease_reference_axn a ON cdr.id = a.chem_disease_reference_id LEFT OUTER JOIN reference r ON cdr.reference_id = r.id LEFT OUTER JOIN term g ON cdr.via_gene_id = g.id GROUP BY c.nm, c.nm_sort, c.acc_txt, c.secondary_nm, d.nm, d.acc_db_cd || ':' || d.acc_txt, a.action_type_nm, g.nm, cdr.network_score ORDER BY c.nm_sort, d.nm;

[ Date: 2024-06-27 09:54:33 - Database: ctdprd51 - User: qaeu - Bind query: yes ]

SELECT /* UniProtGeneMappingsDAO */ lu.acc_txt || ' DR CTD; ' || lg.acc_txt || '; -.' AS mapping FROM db_link lu INNER JOIN db_link lg ON lu.object_id = lg.object_id AND lu.object_type_id = lg.object_type_id WHERE lu.object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'gene') AND lu.db_id = ( SELECT d.id FROM db d WHERE d.cd = 'SPTREM') AND lu.type_cd = 'X' AND lg.db_id = ( SELECT d.id FROM db d WHERE d.cd = 'GENE') AND lg.type_cd = 'A' AND lg.is_primary ORDER BY lu.acc_txt;

[ Date: 2024-06-27 09:44:15 - Database: ctdprd51 - User: qaeu - Bind query: yes ]