clns-eTarget/src/main/sql/eTargetNG.sql [164:201]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ALTER VIEW CTDNA_SUBSETS AS
SELECT 
specimen.person_id AS person_id,
specimen.specimen_id AS specimen_id,
specimen.specimen_concept_id AS specimen_concept_id,
specimen.specimen_date AS specimen_date,
geneVar.measurement_gene_variant_id as gene_varient_id,
geneVar.ngs_gene_present  AS ngs_gene_present,
geneVar.cdna_change  AS cdna_change,
geneName.gene_name  AS gene_name,
geneVar.amino_acid_change  AS amino_acid_change,
geneVar.cfdna_frequency AS cfdna_frequency,
geneVar.total_cfdna_reads AS total_cfdna_reads,
geneVar.germline_frequency AS germline_frequency,
geneVar.mutation_type AS mutation_type,
geneVar.high_confidence AS high_confidence,
geneVar.is_specific_mutation_in_panel AS is_specific_mutation_in_panel,
geneVar.sv_genome_position as sv_genome_position,
geneVar.cosmic_url as cosmic_url,
genePanel.ngs_library_cfdna_input AS ngs_library_cfdna_input,
genePanel.cfdna_input_colour_key AS cfdna_input_colour_key,
genePanel.average_read_depth AS average_read_depth,
genePanel.average_read_depth_colour_key AS average_read_depth_colour_key,
genePanel.ngs_run AS ngs_run,
genePanel.ngs_comment AS ngs_comment,
genePanel.exploratory_comment AS exploratory_comment,
genePanelConcept.panel_name AS panel_name,
genePanel.bioinformatics_pipeline AS bioinformatics_pipeline,
genePanel.level_of_detection AS level_of_detection,
genePanel.analysis_failed AS analysis_failed,
genePanel.no_mutations_found AS no_mutations_found,
genePanel.run_number AS run_number,
specimen.baseline_number AS baseline_number,
genePanel.measurement_gene_panel_id AS measurement_gene_panel_id
FROM dbo.SPECIMEN AS specimen 
FULL OUTER JOIN dbo.MEASUREMENT_GENE_PANEL AS genePanel ON specimen.specimen_id = genePanel.specimen_id 
FULL OUTER JOIN dbo.MEASUREMENT_GENE_VARIANT AS geneVar ON geneVar.measurement_gene_panel_id = genePanel.measurement_gene_panel_id 
FULL OUTER JOIN dbo.CONCEPT_GENE AS geneName ON geneVar.gene_concept_id = geneName.gene_concept_id 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



clns-eTarget/src/main/sql/updateDB_v1.sql [210:247]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ALTER VIEW CTDNA_SUBSETS AS
SELECT 
specimen.person_id AS person_id,
specimen.specimen_id AS specimen_id,
specimen.specimen_concept_id AS specimen_concept_id,
specimen.specimen_date AS specimen_date,
geneVar.measurement_gene_variant_id as gene_varient_id,
geneVar.ngs_gene_present  AS ngs_gene_present,
geneVar.cdna_change  AS cdna_change,
geneName.gene_name  AS gene_name,
geneVar.amino_acid_change  AS amino_acid_change,
geneVar.cfdna_frequency AS cfdna_frequency,
geneVar.total_cfdna_reads AS total_cfdna_reads,
geneVar.germline_frequency AS germline_frequency,
geneVar.mutation_type AS mutation_type,
geneVar.high_confidence AS high_confidence,
geneVar.is_specific_mutation_in_panel AS is_specific_mutation_in_panel,
geneVar.sv_genome_position as sv_genome_position,
geneVar.cosmic_url as cosmic_url,
genePanel.ngs_library_cfdna_input AS ngs_library_cfdna_input,
genePanel.cfdna_input_colour_key AS cfdna_input_colour_key,
genePanel.average_read_depth AS average_read_depth,
genePanel.average_read_depth_colour_key AS average_read_depth_colour_key,
genePanel.ngs_run AS ngs_run,
genePanel.ngs_comment AS ngs_comment,
genePanel.exploratory_comment AS exploratory_comment,
genePanelConcept.panel_name AS panel_name,
genePanel.bioinformatics_pipeline AS bioinformatics_pipeline,
genePanel.level_of_detection AS level_of_detection,
genePanel.analysis_failed AS analysis_failed,
genePanel.no_mutations_found AS no_mutations_found,
genePanel.run_number AS run_number,
specimen.baseline_number AS baseline_number,
genePanel.measurement_gene_panel_id AS measurement_gene_panel_id
FROM dbo.SPECIMEN AS specimen 
FULL OUTER JOIN dbo.MEASUREMENT_GENE_PANEL AS genePanel ON specimen.specimen_id = genePanel.specimen_id 
FULL OUTER JOIN dbo.MEASUREMENT_GENE_VARIANT AS geneVar ON geneVar.measurement_gene_panel_id = genePanel.measurement_gene_panel_id 
FULL OUTER JOIN dbo.CONCEPT_GENE AS geneName ON geneVar.gene_concept_id = geneName.gene_concept_id 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



