Q (haiku): "What is the concordance between ERBB2 copy number amplification, mRNA overexpression, and protein overexpression in breast cancer?" ▶ read_guide { "uri": "cbioportal://clinical-data-guide" } ◀ result # Clinical Data Query Guide ## Overview Clinical data in cBioPortal is stored at both patient and sample levels. Understanding the distinction is crucial for accurate analysis. ## Data Organization ### Patient-Level vs Sample-Level Data - **Patient-level**: Demographics, overall survival, disease stage (stored once per patient) - **Sample-level**: Sample type, sequencing platform, purity (can have multiple per patient) ### Key Tables - `clinical_patient`: Patient-level clinical attributes - `clinical_sample`: Sample-level clinical attributes - `clinical_data_derived`: Pre-joined view combining both levels - `clinical_attribute_meta`: Metadata about available clinical attributes ## Recommended Approach: Use clinical_data_derived The `clinical_data_derived` table is pre-joined and optimized for most queries: ```sql -- Get clinical data for specific attributes SELECT sample_unique_id, patient_unique_id, attribute_name, attribute_value FROM clinical_data_derived WHERE cancer_study_identifier = 'your_study_id' AND attribute_name IN ('CANCER_TYPE', 'SAMPLE_TYPE', 'TMB_NONSYNONYMOUS'); ``` ## Clinical Attribute Discovery ### Use clinical_attribute_meta for Discovering Available Attributes **Always start here** to see what clinical attributes are available for a specific study: ```sql -- Discover available clinical attributes for a study SELECT attr_id, description, patient_attribute, cancer_study_id FROM clinical_attribute_meta WHERE cancer_study_id = ( SELECT cancer_study_id FROM cancer_study WHERE cancer_study_identifier = 'your_study_id' ) ORDER BY patient_attribute, attr_id; ``` **Key Fields:** - **attr_id**: matches attr_id in clinical_sample/clinical_patient tables - **description**: human-readable description of the attribute - **patient_attribute**: true = patient attribute, false = sample attribute - **cancer_study_id**: links to cancer_study table (filter by study) ## Attribute Semantics and Matching ### Case-Insensitive Matching for Attribute Values Clinical values are free text across studies and may differ only by case. For example, a controlled-looking value such as germline mutation status may appear as `GERMLINE`, `Germline`, or another case variant. When filtering `clinical_data_derived.attribute_value`, use case-insensitive matching unless you have already profiled the exact values in the target study: ```sql -- Correct: case-insensitive clinical value filter SELECT DISTINCT sample_unique_id, patient_unique_id FROM clinical_data_derived WHERE cancer_study_identifier = 'your_study_id' AND attribute_name = 'MUTATION_STATUS' AND upper(attribute_value) = 'GERMLINE'; ``` Do not write `attribute_value = 'GERMLINE'` without first checking all distinct values for that attribute in the study. ### Query the Requested Attribute, Not a Proxy Do not infer one clinical attribute from a related subtype or marker. Query the actual requested attribute when it … (17073 more chars) ▶ list_studies { "search": "breast cancer", "limit": 20, "verbose": true } ◀ result {"result":[{"cancer_study_identifier":"breast_msk_2026","name":"CCNE1 Amplifications in Breast Cancer (MSK, 2026)","description":"Targeted Sequencing of 6,318 breast cancer samples and their matched normals via MSK-IMPACT to investigate the clinicopathologic characteristics and prognostic implications of CCNE1 amplifications in breast cancer across receptor subtypes.","type_of_cancer_id":"breast","sample_count":6318,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=breast_msk_2026"},{"cancer_study_identifier":"breast_msk_2025","name":"Breast Cancer (MSK, Nat Genet 2025)","description":"Targeted sequencing of 3879 breast clinical cases and their matched normals via MSK-IMPACT platform.","type_of_cancer_id":"brca","sample_count":3879,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=breast_msk_2025"},{"cancer_study_identifier":"brca_metabric","name":"Breast Cancer (METABRIC, Nature 2012 & Nat Commun 2016)","description":"Targeted sequencing of 2509 primary breast tumors with 548 matched normals.","type_of_cancer_id":"brca","sample_count":2509,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=brca_metabric"},{"cancer_study_identifier":"breast_msk_2018","name":"Breast Cancer (MSK, Cancer Cell 2018)","description":"Targeted Sequencing of tumor/normal sample pairs from 1918 Breast cancers.","type_of_cancer_id":"breast","sample_count":1918,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=breast_msk_2018"},{"cancer_study_identifier":"breast_ink4_msk_2021","name":"Metastatic Breast Cancer (MSK, Cancer Discovery 2022)","description":"Targeted Sequencing of 1365 metastatic Breast Cancer tumor/normal pairs via MSK-IMPACT to understand the role of INK4 on CDK4/6 resistance.","type_of_cancer_id":"breast","sample_count":1365,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=breast_ink4_msk_2021"},{"cancer_study_identifier":"brca_tcga_pub","name":"Breast Invasive Carcinoma (TCGA, Nature 2012)","description":"Whole-exome sequencing (510 samples with matched normals), genomic DNA copy number arrays, DNA methylation, messenger RNA arrays, microRNA sequencing and reverse-phase protein arrays analysis on 825 primary breast cancer samples. The Cancer Genome Atlas (TCGA) Breast Invasive Carcinoma project. 825 cases.
Nature 2012. Raw data via the TCGA Data Portal.","type_of_cancer_id":"brca","sample_count":825,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=brca_tcga_pub"},{"cancer_study_identifier":"brca_mbcproject_2022","name":"The Metastatic Breast Cancer Project (Provisional, December 2021)","description":"The Metastatic Breast Cancer Project (www.mbcproject.org) is an ongoing patient-driven initiative. This dataset release is derived from 379 samples, including both primary and/or me … (7417 more chars) ▶ clickhouse_list_tables {} ◀ result {"tables":[{"name":"allele_specific_copy_number"},{"name":"alteration_driver_annotation"},{"name":"cancer_study"},{"name":"cancer_study_query_preferences"},{"name":"cancer_study_tags"},{"name":"cbioportal_sequence_state"},{"name":"clinical_attribute_counts"},{"name":"clinical_attribute_meta"},{"name":"clinical_data_derived"},{"name":"clinical_event"},{"name":"clinical_event_data"},{"name":"clinical_event_data_derived"},{"name":"clinical_event_derived"},{"name":"clinical_patient"},{"name":"clinical_sample"},{"name":"cna_event"},{"name":"cna_panel_gene_coverage"},{"name":"cna_wes_coverage"},{"name":"co_altered_genes_in_study"},{"name":"copy_number_seg"},{"name":"copy_number_seg_file"},{"name":"gene"},{"name":"gene_alias"},{"name":"gene_alteration_frequency_by_cancer_type"},{"name":"gene_cna_distribution_in_study"},{"name":"gene_mutation_frequency_by_cancer_type"},{"name":"gene_mutation_frequency_in_studies"},{"name":"gene_mutation_frequency_in_study"},{"name":"gene_mutation_variants_in_study"},{"name":"gene_pair_coexpression"},{"name":"gene_panel"},{"name":"gene_panel_list"},{"name":"gene_panel_to_gene_derived"},{"name":"generic_assay_data_derived"},{"name":"generic_assay_meta_derived"},{"name":"generic_assay_profile_entity_derived"},{"name":"generic_entity_properties"},{"name":"geneset"},{"name":"geneset_gene"},{"name":"geneset_hierarchy_leaf"},{"name":"geneset_hierarchy_node"},{"name":"genetic_alteration"},{"name":"genetic_alteration_derived"},{"name":"genetic_entity"},{"name":"genetic_profile"},{"name":"genetic_profile_link"},{"name":"genetic_profile_samples"},{"name":"genomic_event_derived"},{"name":"gistic"},{"name":"gistic_to_gene"},{"name":"info"},{"name":"mut_sig"},{"name":"mutation"},{"name":"mutation_count_by_keyword"},{"name":"mutation_derived"},{"name":"mutation_event"},{"name":"mutation_panel_gene_coverage"},{"name":"mutation_wes_coverage"},{"name":"patient"},{"name":"reference_genome"},{"name":"reference_genome_gene"},{"name":"resource_definition"},{"name":"resource_patient"},{"name":"resource_sample"},{"name":"resource_study"},{"name":"sample"},{"name":"sample_cna_event"},{"name":"sample_derived"},{"name":"sample_list"},{"name":"sample_list_list"},{"name":"sample_profile"},{"name":"sample_to_gene_panel_derived"},{"name":"structural_variant"},{"name":"sv_panel_gene_coverage"},{"name":"sv_wes_coverage"},{"name":"top_cna_genes_in_study"},{"name":"top_mutated_genes_in_cohort"},{"name":"top_mutated_genes_in_study"},{"name":"top_sv_genes_in_study"},{"name":"treatment_counts_in_study"},{"name":"treatment_regimens_in_study"},{"name":"type_of_cancer"}]} ▶ clickhouse_run_select_query { "query": " -- Find breast cancer studies with ERBB2 data across CNA, mRNA, and protein SELECT DISTINCT cs.cancer_study_identifier, cs.name, gp.genetic_profile_id, gp.genetic_alteration_type, gp.data_type FROM cancer_study cs JOIN genetic_profile gp ON cs.cancer_study_id = gp.cancer_study_id WHERE cs.type_of_cancer_id IN ('brca', 'breast') AND gp.genetic_alteration_type IN ('COPY_NUMBER_ALTERATION', 'mRNA_EXPRESSION', 'PROTEIN_LEVEL') AND ( LOWER(gp.genetic_profile_id) LIKE '%rna%' OR LOWER(gp.genetic_profile_id) LIKE '%mrna%' OR LOWER(gp.genetic_profile_id) LIKE '%protein%' OR LOWER(gp.genetic_profile_id) LIKE '%cna%' OR LOWER(gp.genetic_profile_id) LIKE '%gistic%' OR LOWER(gp.genetic_profile_id) LIKE '%rppa%' ) ORDER BY cs.cancer_study_identifier, gp.genetic_alteration_type LIMIT 100; ", "max_rows": 100 } ◀ result {"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 47\n Code: 47. DB::Exception: Identifier 'gp.data_type' cannot be resolved from table with name gp. In scope SELECT DISTINCT cs.cancer_study_identifier, cs.name, gp.genetic_profile_id, gp.genetic_alteration_type, gp.data_type FROM cancer_study AS cs INNER JOIN genetic_profile AS gp ON cs.cancer_study_id = gp.cancer_study_id WHERE (cs.type_of_cancer_id IN ('brca', 'breast')) AND (gp.genetic_alteration_type IN ('COPY_NUMBER_ALTERATION', 'mRNA_EXPRESSION', 'PROTEIN_LEVEL')) AND ((LOWER(gp.genetic_profile_id) LIKE '%rna%') OR (LOWER(gp.genetic_profile_id) LIKE '%mrna%') OR (LOWER(gp.genetic_profile_id) LIKE '%protein%') OR (LOWER(gp.genetic_profile_id) LIKE '%cna%') OR (LOWER(gp.genetic_profile_id) LIKE '%gistic%') OR (LOWER(gp.genetic_profile_id) LIKE '%rppa%')) ORDER BY cs.cancer_study_identifier ASC, gp.genetic_alteration_type ASC LIMIT 100. Maybe you meant: ['gp.datatype']. (UNKNOWN_IDENTIFIER) (version 26.4.1.2359 (official build))\n"} ▶ clickhouse_list_table_columns { "table": "genetic_profile" } ◀ result {"columns":[{"name":"genetic_profile_id","type":"Int64"},{"name":"stable_id","type":"String"},{"name":"cancer_study_id","type":"Int64"},{"name":"genetic_alteration_type","type":"String"},{"name":"generic_assay_type","type":"Nullable(String)"},{"name":"datatype","type":"String"},{"name":"name","type":"String"},{"name":"description","type":"Nullable(String)"},{"name":"show_profile_in_analysis_tab","type":"Int32"},{"name":"pivot_threshold","type":"Nullable(Float64)"},{"name":"sort_order","type":"Nullable(String)"},{"name":"patient_level","type":"Nullable(Int32)"}]} ▶ clickhouse_run_select_query { "query": " -- Find breast cancer studies with ERBB2 data across CNA, mRNA, and protein SELECT DISTINCT cs.cancer_study_identifier, cs.name, gp.stable_id, gp.genetic_alteration_type, gp.datatype FROM cancer_study cs JOIN genetic_profile gp ON cs.cancer_study_id = gp.cancer_study_id WHERE cs.type_of_cancer_id IN ('brca', 'breast') AND gp.genetic_alteration_type IN ('COPY_NUMBER_ALTERATION', 'mRNA_EXPRESSION', 'PROTEIN_LEVEL') ORDER BY cs.cancer_study_identifier, gp.genetic_alteration_type LIMIT 50; ", "max_rows": 50 } ◀ result {"rows":[{"cancer_study_identifier":"brca_aurora_2023","name":"Metastatic Breast Cancer (AURORA US Network, Nat Cancer 2023)","stable_id":"brca_aurora_2023_log2CNA","genetic_alteration_type":"COPY_NUMBER_ALTERATION","datatype":"LOG2-VALUE"},{"cancer_study_identifier":"brca_bccrc_xenograft_2014","name":"Breast Cancer Xenografts (British Columbia, Nature 2015)","stable_id":"brca_bccrc_xenograft_2014_log2CNA","genetic_alteration_type":"COPY_NUMBER_ALTERATION","datatype":"LOG2-VALUE"},{"cancer_study_identifier":"brca_cptac_2020","name":"Proteogenomic landscape of breast cancer (CPTAC, Cell 2020)","stable_id":"brca_cptac_2020_gistic","genetic_alteration_type":"COPY_NUMBER_ALTERATION","datatype":"DISCRETE"},{"cancer_study_identifier":"brca_cptac_2020","name":"Proteogenomic landscape of breast cancer (CPTAC, Cell 2020)","stable_id":"brca_cptac_2020_log2CNA","genetic_alteration_type":"COPY_NUMBER_ALTERATION","datatype":"LOG2-VALUE"},{"cancer_study_identifier":"brca_cptac_2020","name":"Proteogenomic landscape of breast cancer (CPTAC, Cell 2020)","stable_id":"brca_cptac_2020_protein_quantification","genetic_alteration_type":"PROTEIN_LEVEL","datatype":"LOG2-VALUE"},{"cancer_study_identifier":"brca_dldccc_2022","name":"Triple Negative Breast Cancer (DLDCCC, Cancer Discov 2022)","stable_id":"brca_dldccc_2022_log2CNA","genetic_alteration_type":"COPY_NUMBER_ALTERATION","datatype":"LOG2-VALUE"},{"cancer_study_identifier":"brca_dldccc_2022","name":"Triple Negative Breast Cancer (DLDCCC, Cancer Discov 2022)","stable_id":"brca_dldccc_2022_gistic","genetic_alteration_type":"COPY_NUMBER_ALTERATION","datatype":"DISCRETE"},{"cancer_study_identifier":"brca_dldccc_2022","name":"Triple Negative Breast Cancer (DLDCCC, Cancer Discov 2022)","stable_id":"brca_dldccc_2022_protein_quantification_zscores","genetic_alteration_type":"PROTEIN_LEVEL","datatype":"Z-SCORE"},{"cancer_study_identifier":"brca_dldccc_2022","name":"Triple Negative Breast Cancer (DLDCCC, Cancer Discov 2022)","stable_id":"brca_dldccc_2022_protein_quantification","genetic_alteration_type":"PROTEIN_LEVEL","datatype":"CONTINUOUS"},{"cancer_study_identifier":"brca_hta9_htan_2022","name":"Breast Cancer (HTAN, 2022)","stable_id":"brca_hta9_htan_2022_cna","genetic_alteration_type":"COPY_NUMBER_ALTERATION","datatype":"DISCRETE"},{"cancer_study_identifier":"brca_igr_2015","name":"Metastatic Breast Cancer (INSERM, PLoS Med 2016)","stable_id":"brca_igr_2015_cna","genetic_alteration_type":"COPY_NUMBER_ALTERATION","datatype":"DISCRETE"},{"cancer_study_identifier":"brca_mapk_hp_msk_2021","name":"MAPK on resistance to anti-HER2 therapy for breast cancer (MSK, Nat Commun. 2022)","stable_id":"brca_mapk_hp_msk_2021_cna","genetic_alteration_type":"COPY_NUMBER_ALTERATION","datatype":"DISCRETE"},{"cancer_study_identifier":"brca_mbcproject_2022","name":"The Metastatic Breast Cancer Project (Provisional, December 2021)","stable_id":"brca_mbcproject_2022_gistic","genetic_alteration_type":"COPY_NUMBER_ALTERATION","datatype":"DISCRET … (7234 more chars) ▶ clickhouse_run_select_query { "query": " -- Find breast cancer studies with mRNA profiles SELECT DISTINCT cs.cancer_study_identifier, cs.name, gp.stable_id, gp.genetic_alteration_type, gp.datatype FROM cancer_study cs JOIN genetic_profile gp ON cs.cancer_study_id = gp.cancer_study_id WHERE cs.type_of_cancer_id IN ('brca', 'breast') AND gp.genetic_alteration_type = 'mRNA_EXPRESSION' ORDER BY cs.cancer_study_identifier LIMIT 20; ", "max_rows": 20 } ◀ result {"rows":[]} ▶ clickhouse_run_select_query { "query": " -- Find all profile types available in breast cancer studies SELECT DISTINCT cs.cancer_study_identifier, gp.genetic_alteration_type FROM cancer_study cs JOIN genetic_profile gp ON cs.cancer_study_id = gp.cancer_study_id WHERE cs.type_of_cancer_id IN ('brca', 'breast') GROUP BY cs.cancer_study_identifier, gp.genetic_alteration_type ORDER BY cs.cancer_study_identifier; ", "max_rows": 50 } ◀ result {"rows":[{"cancer_study_identifier":"brca_aurora_2023","genetic_alteration_type":"GENERIC_ASSAY"},{"cancer_study_identifier":"brca_aurora_2023","genetic_alteration_type":"COPY_NUMBER_ALTERATION"},{"cancer_study_identifier":"brca_aurora_2023","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_aurora_2023","genetic_alteration_type":"MRNA_EXPRESSION"},{"cancer_study_identifier":"brca_bccrc","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_bccrc_xenograft_2014","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_bccrc_xenograft_2014","genetic_alteration_type":"COPY_NUMBER_ALTERATION"},{"cancer_study_identifier":"brca_broad","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_cptac_2020","genetic_alteration_type":"GENERIC_ASSAY"},{"cancer_study_identifier":"brca_cptac_2020","genetic_alteration_type":"PROTEIN_LEVEL"},{"cancer_study_identifier":"brca_cptac_2020","genetic_alteration_type":"COPY_NUMBER_ALTERATION"},{"cancer_study_identifier":"brca_cptac_2020","genetic_alteration_type":"MRNA_EXPRESSION"},{"cancer_study_identifier":"brca_cptac_2020","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_dfci_2020","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_dldccc_2022","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_dldccc_2022","genetic_alteration_type":"MRNA_EXPRESSION"},{"cancer_study_identifier":"brca_dldccc_2022","genetic_alteration_type":"PROTEIN_LEVEL"},{"cancer_study_identifier":"brca_dldccc_2022","genetic_alteration_type":"COPY_NUMBER_ALTERATION"},{"cancer_study_identifier":"brca_fuscc_2020","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_hta9_htan_2022","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_hta9_htan_2022","genetic_alteration_type":"COPY_NUMBER_ALTERATION"},{"cancer_study_identifier":"brca_iatlas_anders_2022","genetic_alteration_type":"MRNA_EXPRESSION"},{"cancer_study_identifier":"brca_iatlas_anders_2022","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_iatlas_anders_2022","genetic_alteration_type":"GENERIC_ASSAY"},{"cancer_study_identifier":"brca_igr_2015","genetic_alteration_type":"COPY_NUMBER_ALTERATION"},{"cancer_study_identifier":"brca_igr_2015","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_jup_msk_2020","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_mapk_hp_msk_2021","genetic_alteration_type":"STRUCTURAL_VARIANT"},{"cancer_study_identifier":"brca_mapk_hp_msk_2021","genetic_alteration_type":"MUTATION_EXTENDED"},{"cancer_study_identifier":"brca_mapk_hp_msk_2021","genetic_alteration_type":"COPY_NUMBER_ALTERATION"},{"cancer_study_identifier":"brca_mbcproject_2022","genetic_alteration_type":"COPY_NUMBER_ALTERATION"},{"cancer_study_identifier":"brca_mbcproject_2022","genet … (2051 more chars) ▶ clickhouse_run_select_query { "query": " -- Query ERBB2 concordance in TCGA BRCA Pan-Cancer Atlas -- CNA, mRNA, and protein levels by sample WITH erbb2_data AS ( SELECT sd.sample_unique_id, sd.patient_unique_id, MAX(CASE WHEN gp.genetic_alteration_type = 'COPY_NUMBER_ALTERATION' AND gp.stable_id LIKE '%gistic%' THEN toFloat64OrNull(gd.value) END) AS cna_value, MAX(CASE WHEN gp.genetic_alteration_type = 'MRNA_EXPRESSION' THEN toFloat64OrNull(gd.value) END) AS mrna_value, MAX(CASE WHEN gp.genetic_alteration_type = 'PROTEIN_LEVEL' AND gp.stable_id LIKE '%rppa%' THEN toFloat64OrNull(gd.value) END) AS protein_value FROM genomic_event_derived gd JOIN sample_derived sd ON gd.sample_unique_id = sd.sample_unique_id JOIN genetic_profile gp ON gd.genetic_profile_id = gp.genetic_profile_id WHERE sd.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018' AND gd.hugo_gene_symbol = 'ERBB2' GROUP BY sd.sample_unique_id, sd.patient_unique_id ) SELECT COUNT(*) AS total_samples, SUM(CASE WHEN cna_value IS NOT NULL THEN 1 ELSE 0 END) AS samples_with_cna, SUM(CASE WHEN mrna_value IS NOT NULL THEN 1 ELSE 0 END) AS samples_with_mrna, SUM(CASE WHEN protein_value IS NOT NULL THEN 1 ELSE 0 END) AS samples_with_protein, SUM(CASE WHEN cna_value IS NOT NULL AND mrna_value IS NOT NULL THEN 1 ELSE 0 END) AS cna_and_mrna, SUM(CASE WHEN cna_value IS NOT NULL AND protein_value IS NOT NULL THEN 1 ELSE 0 END) AS cna_and_protein, SUM(CASE WHEN mrna_value IS NOT NULL AND protein_value IS NOT NULL THEN 1 ELSE 0 END) AS mrna_and_protein, SUM(CASE WHEN cna_value IS NOT NULL AND mrna_value IS NOT NULL AND protein_value IS NOT NULL THEN 1 ELSE 0 END) AS all_three FROM erbb2_data; ", "max_rows": 10 } ◀ result {"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 47\n Code: 47. DB::Exception: Identifier 'gd.genetic_profile_id' cannot be resolved from table with name gd. In scope erbb2_data. Maybe you meant: ['gd.genetic_profile_stable_id']. (UNKNOWN_IDENTIFIER) (version 26.4.1.2359 (official build))\n"} ▶ clickhouse_list_table_columns { "table": "genomic_event_derived" } ◀ result {"columns":[{"name":"sample_unique_id","type":"String"},{"name":"hugo_gene_symbol","type":"String","comment":"HUGO gene symbol (e.g., TP53, KRAS, BRAF). Use for gene-specific queries."},{"name":"entrez_gene_id","type":"Int32"},{"name":"gene_panel_stable_id","type":"LowCardinality(String)"},{"name":"cancer_study_identifier","type":"LowCardinality(String)"},{"name":"genetic_profile_stable_id","type":"LowCardinality(String)"},{"name":"variant_type","type":"LowCardinality(String)","comment":"Type of genomic event: \"mutation\" for SNVs/indels, \"cna\" for copy number alterations, \"structural_variant\" for SVs. Always filter by this."},{"name":"mutation_variant","type":"String","comment":"Protein change notation (e.g., p.V600E, p.R175H). Use for specific variant queries. \"NA\" for non-mutation events."},{"name":"mutation_type","type":"LowCardinality(String)"},{"name":"mutation_status","type":"LowCardinality(String)","comment":"For mutations: Somatic, Germline, UNKNOWN, or UNCALLED. Filter mutation_status != \"UNCALLED\" to exclude uncertain calls. Include all other statuses."},{"name":"driver_filter","type":"LowCardinality(String)"},{"name":"driver_filter_annotation","type":"String"},{"name":"driver_tiers_filter","type":"LowCardinality(String)"},{"name":"driver_tiers_filter_annotation","type":"String"},{"name":"cna_alteration","type":"Nullable(Int8)","comment":"Copy number alteration: only 2 = amplification (AMP) and -2 = deep deletion (HOMDEL) are stored. Shallow deletion (-1), diploid (0) and gain (1) are not in this table; query genetic_alteration_derived WHERE profile_type = 'gistic' (alteration_value is a String, e.g. '-1'). NULL for non-CNA events."},{"name":"cna_cytoband","type":"String"},{"name":"sv_event_info","type":"String"},{"name":"patient_unique_id","type":"String"},{"name":"off_panel","type":"Bool","comment":"Boolean: 1 = mutation outside gene panel coverage (off-panel), 0 = within panel (on-panel). Filter off_panel = 0 for reliable frequency calculations."}]} ▶ clickhouse_list_table_columns { "table": "genetic_alteration_derived" } ◀ result {"columns":[{"name":"sample_unique_id","type":"String"},{"name":"cancer_study_identifier","type":"LowCardinality(String)"},{"name":"hugo_gene_symbol","type":"String"},{"name":"profile_type","type":"LowCardinality(String)"},{"name":"alteration_value","type":"Nullable(String)"}]} ▶ clickhouse_run_select_query { "query": " -- ERBB2 concordance analysis: CNA (GISTIC), mRNA, and protein in TCGA BRCA -- profile_type values: 'gistic', 'mrna_seq_v2_mrna', 'protein_mass_spec', etc. WITH erbb2_data AS ( SELECT sample_unique_id, MAX(CASE WHEN profile_type LIKE '%gistic%' OR profile_type LIKE '%cna%' THEN toInt32OrNull(alteration_value) END) AS cna_gistic, MAX(CASE WHEN profile_type LIKE '%mrna%' OR profile_type LIKE '%expression%' THEN toFloat64OrNull(alteration_value) END) AS mrna_expr, MAX(CASE WHEN profile_type LIKE '%rppa%' OR profile_type LIKE '%protein%' THEN toFloat64OrNull(alteration_value) END) AS protein_level FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018' AND hugo_gene_symbol = 'ERBB2' GROUP BY sample_unique_id ) SELECT COUNT(*) AS total_samples, SUM(CASE WHEN cna_gistic IS NOT NULL THEN 1 ELSE 0 END) AS samples_with_cna, SUM(CASE WHEN mrna_expr IS NOT NULL THEN 1 ELSE 0 END) AS samples_with_mrna, SUM(CASE WHEN protein_level IS NOT NULL THEN 1 ELSE 0 END) AS samples_with_protein, SUM(CASE WHEN cna_gistic IS NOT NULL AND mrna_expr IS NOT NULL THEN 1 ELSE 0 END) AS both_cna_mrna, SUM(CASE WHEN cna_gistic IS NOT NULL AND protein_level IS NOT NULL THEN 1 ELSE 0 END) AS both_cna_protein, SUM(CASE WHEN mrna_expr IS NOT NULL AND protein_level IS NOT NULL THEN 1 ELSE 0 END) AS both_mrna_protein, SUM(CASE WHEN cna_gistic IS NOT NULL AND mrna_expr IS NOT NULL AND protein_level IS NOT NULL THEN 1 ELSE 0 END) AS all_three_data FROM erbb2_data; ", "max_rows": 5 } ◀ result {"rows":[{"total_samples":1084,"samples_with_cna":1070,"samples_with_mrna":1082,"samples_with_protein":876,"both_cna_mrna":1068,"both_cna_protein":865,"both_mrna_protein":874,"all_three_data":863}]} ▶ clickhouse_run_select_query { "query": " -- ERBB2 concordance: amplified (CNA ≥ 1) vs expression levels -- Use GISTIC values: 2 = AMP, 1 = GAIN, 0 = diploid, -1 = loss, -2 = HOMDEL WITH erbb2_classified AS ( SELECT sample_unique_id, MAX(CASE WHEN profile_type LIKE '%gistic%' THEN toInt32OrNull(alteration_value) END) AS cna_gistic, MAX(CASE WHEN profile_type LIKE '%mrna%' OR profile_type LIKE '%expression%' THEN toFloat64OrNull(alteration_value) END) AS mrna_zscore, MAX(CASE WHEN profile_type LIKE '%rppa%' OR profile_type LIKE '%protein%' THEN toFloat64OrNull(alteration_value) END) AS protein_zscore FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018' AND hugo_gene_symbol = 'ERBB2' GROUP BY sample_unique_id ), amplification_status AS ( SELECT sample_unique_id, cna_gistic, CASE WHEN cna_gistic >= 1 THEN 'Amplified' ELSE 'Not_Amplified' END AS amp_status, mrna_zscore, protein_zscore FROM erbb2_classified WHERE cna_gistic IS NOT NULL ) SELECT amp_status, COUNT(*) AS n_samples, ROUND(AVG(mrna_zscore), 2) AS avg_mrna_zscore, ROUND(median(mrna_zscore), 2) AS median_mrna_zscore, ROUND(MIN(mrna_zscore), 2) AS min_mrna_zscore, ROUND(MAX(mrna_zscore), 2) AS max_mrna_zscore, ROUND(AVG(protein_zscore), 2) AS avg_protein_zscore, ROUND(median(protein_zscore), 2) AS median_protein_zscore, SUM(CASE WHEN mrna_zscore IS NOT NULL THEN 1 ELSE 0 END) AS with_mrna_data, SUM(CASE WHEN protein_zscore IS NOT NULL THEN 1 ELSE 0 END) AS with_protein_data FROM amplification_status WHERE mrna_zscore IS NOT NULL OR protein_zscore IS NOT NULL GROUP BY amp_status ORDER BY amp_status DESC; ", "max_rows": 5 } ◀ result {"rows":[{"amp_status":"Not_Amplified","n_samples":741,"avg_mrna_zscore":6334.95,"median_mrna_zscore":5930,"min_mrna_zscore":154.85,"max_mrna_zscore":25337.7,"avg_protein_zscore":0.5,"median_protein_zscore":0.5,"with_mrna_data":740,"with_protein_data":590},{"amp_status":"Amplified","n_samples":329,"avg_mrna_zscore":44769.1,"median_mrna_zscore":12420.05,"min_mrna_zscore":1606.31,"max_mrna_zscore":380668,"avg_protein_zscore":1.57,"median_protein_zscore":1.24,"with_mrna_data":328,"with_protein_data":275}]} ▶ clickhouse_run_select_query { "query": " -- Check ERBB2 data format: sample the actual values SELECT sample_unique_id, profile_type, alteration_value, length(alteration_value) AS val_length FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018' AND hugo_gene_symbol = 'ERBB2' ORDER BY profile_type, sample_unique_id LIMIT 30; ", "max_rows": 30 } ◀ result {"rows":[{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-3C-AAAU-01","profile_type":"gistic","alteration_value":"0","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-3C-AALI-01","profile_type":"gistic","alteration_value":"1","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-3C-AALJ-01","profile_type":"gistic","alteration_value":"2","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-3C-AALK-01","profile_type":"gistic","alteration_value":"2","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-4H-AAAK-01","profile_type":"gistic","alteration_value":"1","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-5L-AAT0-01","profile_type":"gistic","alteration_value":"0","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-5T-A9QA-01","profile_type":"gistic","alteration_value":"1","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SB-01","profile_type":"gistic","alteration_value":"0","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SD-01","profile_type":"gistic","alteration_value":"0","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SE-01","profile_type":"gistic","alteration_value":"0","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SF-01","profile_type":"gistic","alteration_value":"0","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SG-01","profile_type":"gistic","alteration_value":"0","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SH-01","profile_type":"gistic","alteration_value":"1","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SI-01","profile_type":"gistic","alteration_value":"-1","val_length":2},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SJ-01","profile_type":"gistic","alteration_value":"1","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SK-01","profile_type":"gistic","alteration_value":"-1","val_length":2},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SM-01","profile_type":"gistic","alteration_value":"2","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SN-01","profile_type":"gistic","alteration_value":"2","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SO-01","profile_type":"gistic","alteration_value":"1","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SP-01","profile_type":"gistic","alteration_value":"-1","val_length":2},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SQ-01","profile_type":"gistic","alteration_value":"0","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A04N-01","profile_type":"gistic","alteration_value":"0","val_length":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A04P-01","profile_type":"gistic","alteration_value":"-1","val_length": … (916 more chars) ▶ clickhouse_run_select_query { "query": " -- Check distinct profile types for ERBB2 in TCGA BRCA SELECT DISTINCT profile_type FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018' AND hugo_gene_symbol = 'ERBB2' ORDER BY profile_type; ", "max_rows": 10 } ◀ result {"rows":[{"profile_type":"gistic"},{"profile_type":"log2CNA"},{"profile_type":"protein_quantification"},{"profile_type":"protein_quantification_zscores"},{"profile_type":"rna_seq_v2_mrna"},{"profile_type":"rna_seq_v2_mrna_median_Zscores"},{"profile_type":"rna_seq_v2_mrna_median_all_sample_Zscores"},{"profile_type":"rna_seq_v2_mrna_median_all_sample_ref_normal_Zscores"},{"profile_type":"rppa"},{"profile_type":"rppa_Zscores"}]} ▶ clickhouse_run_select_query { "query": " -- ERBB2 concordance analysis: CNA amplification vs expression overexpression -- TCGA BRCA PanCancer Atlas WITH erbb2_aligned AS ( SELECT sample_unique_id, MAX(CASE WHEN profile_type = 'gistic' THEN toInt32(alteration_value) END) AS cna_gistic, MAX(CASE WHEN profile_type = 'rna_seq_v2_mrna_median_Zscores' THEN toFloat64(alteration_value) END) AS mrna_zscore, MAX(CASE WHEN profile_type = 'protein_quantification_zscores' THEN toFloat64(alteration_value) END) AS protein_zscore FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018' AND hugo_gene_symbol = 'ERBB2' GROUP BY sample_unique_id ), classified AS ( SELECT cna_gistic, mrna_zscore, protein_zscore, CASE WHEN cna_gistic = 2 THEN 'AMP' WHEN cna_gistic = 1 THEN 'GAIN' WHEN cna_gistic = 0 THEN 'DIPLOID' WHEN cna_gistic = -1 THEN 'LOSS' WHEN cna_gistic = -2 THEN 'HOMDEL' ELSE 'UNKNOWN' END AS cna_status, CASE WHEN mrna_zscore > 2 THEN 'HIGH' WHEN mrna_zscore < -2 THEN 'LOW' ELSE 'NORMAL' END AS mrna_status, CASE WHEN protein_zscore > 2 THEN 'HIGH' WHEN protein_zscore < -2 THEN 'LOW' ELSE 'NORMAL' END AS protein_status FROM erbb2_aligned WHERE cna_gistic IS NOT NULL ) SELECT cna_status, COUNT(*) AS n_samples, SUM(CASE WHEN mrna_zscore IS NOT NULL THEN 1 ELSE 0 END) AS mrna_profiled, SUM(CASE WHEN protein_zscore IS NOT NULL THEN 1 ELSE 0 END) AS protein_profiled, ROUND(AVG(mrna_zscore), 2) AS mean_mrna_zscore, ROUND(MAX(mrna_zscore), 2) AS max_mrna, ROUND(MIN(mrna_zscore), 2) AS min_mrna, ROUND(AVG(protein_zscore), 2) AS mean_protein_zscore, ROUND(MAX(protein_zscore), 2) AS max_protein, ROUND(MIN(protein_zscore), 2) AS min_protein FROM classified GROUP BY cna_status ORDER BY CASE WHEN cna_status = 'AMP' THEN 1 WHEN cna_status = 'GAIN' THEN 2 WHEN cna_status = 'DIPLOID' THEN 3 WHEN cna_status = 'LOSS' THEN 4 WHEN cna_status = 'HOMDEL' THEN 5 ELSE 6 END; ", "max_rows": 10 } ◀ result {"rows":[{"cna_status":"AMP","n_samples":123,"mrna_profiled":122,"protein_profiled":20,"mean_mrna_zscore":27.22,"max_mrna":113.26,"min_mrna":-1.17,"mean_protein_zscore":1.54,"max_protein":3.49,"min_protein":-0.01},{"cna_status":"GAIN","n_samples":206,"mrna_profiled":206,"protein_profiled":24,"mean_mrna_zscore":1.91,"max_mrna":63.61,"min_mrna":-1.78,"mean_protein_zscore":-0.05,"max_protein":2.37,"min_protein":-1.17},{"cna_status":"DIPLOID","n_samples":481,"mrna_profiled":480,"protein_profiled":31,"mean_mrna_zscore":0,"max_mrna":5.43,"min_mrna":-2.12,"mean_protein_zscore":-0.36,"max_protein":0.57,"min_protein":-0.88},{"cna_status":"LOSS","n_samples":260,"mrna_profiled":260,"protein_profiled":30,"mean_mrna_zscore":-0.97,"max_mrna":1.25,"min_mrna":-2.22,"mean_protein_zscore":-0.61,"max_protein":0.31,"min_protein":-2.06}]} ▶ clickhouse_run_select_query { "query": " -- ERBB2 concordance: count samples with matching alterations -- TCGA BRCA PanCancer Atlas (863 samples with all three data types) WITH erbb2_aligned AS ( SELECT sample_unique_id, MAX(CASE WHEN profile_type = 'gistic' THEN toInt32(alteration_value) END) AS cna_gistic, MAX(CASE WHEN profile_type = 'rna_seq_v2_mrna_median_Zscores' THEN toFloat64(alteration_value) END) AS mrna_zscore, MAX(CASE WHEN profile_type = 'protein_quantification_zscores' THEN toFloat64(alteration_value) END) AS protein_zscore FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018' AND hugo_gene_symbol = 'ERBB2' GROUP BY sample_unique_id ), with_all_data AS ( SELECT * FROM erbb2_aligned WHERE cna_gistic IS NOT NULL AND mrna_zscore IS NOT NULL AND protein_zscore IS NOT NULL ), classified AS ( SELECT CASE WHEN cna_gistic >= 1 THEN 'Amplified_or_Gain' ELSE 'Not_Amplified' END AS cna_class, CASE WHEN mrna_zscore > 2 THEN 'mRNA_High' ELSE 'mRNA_Not_High' END AS mrna_class, CASE WHEN protein_zscore > 2 THEN 'Protein_High' ELSE 'Protein_Not_High' END AS protein_class FROM with_all_data ) SELECT cna_class, mrna_class, protein_class, COUNT(*) AS n_samples, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct_of_all FROM classified GROUP BY cna_class, mrna_class, protein_class ORDER BY n_samples DESC; ", "max_rows": 20 } ◀ result {"rows":[{"cna_class":"Not_Amplified","mrna_class":"mRNA_Not_High","protein_class":"Protein_Not_High","n_samples":61,"pct_of_all":58.1},{"cna_class":"Amplified_or_Gain","mrna_class":"mRNA_Not_High","protein_class":"Protein_Not_High","n_samples":20,"pct_of_all":19.05},{"cna_class":"Amplified_or_Gain","mrna_class":"mRNA_High","protein_class":"Protein_Not_High","n_samples":17,"pct_of_all":16.19},{"cna_class":"Amplified_or_Gain","mrna_class":"mRNA_High","protein_class":"Protein_High","n_samples":7,"pct_of_all":6.67}]} ▶ clickhouse_run_select_query { "query": " -- ERBB2 concordance summary and correlation metrics -- TCGA BRCA PanCancer Atlas WITH erbb2_aligned AS ( SELECT sample_unique_id, MAX(CASE WHEN profile_type = 'gistic' THEN toInt32(alteration_value) END) AS cna_gistic, MAX(CASE WHEN profile_type = 'rna_seq_v2_mrna_median_Zscores' THEN toFloat64(alteration_value) END) AS mrna_zscore, MAX(CASE WHEN profile_type = 'protein_quantification_zscores' THEN toFloat64(alteration_value) END) AS protein_zscore FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018' AND hugo_gene_symbol = 'ERBB2' GROUP BY sample_unique_id ), with_all_data AS ( SELECT * FROM erbb2_aligned WHERE cna_gistic IS NOT NULL AND mrna_zscore IS NOT NULL AND protein_zscore IS NOT NULL ), concordance_calculations AS ( SELECT COUNT(*) AS total_n, -- Concordant: AMP/GAIN with HIGH mRNA + protein SUM(CASE WHEN cna_gistic >= 1 AND mrna_zscore > 2 AND protein_zscore > 2 THEN 1 ELSE 0 END) AS all_three_high, -- AMP/GAIN with high mRNA (regardless of protein) SUM(CASE WHEN cna_gistic >= 1 AND mrna_zscore > 2 THEN 1 ELSE 0 END) AS amp_and_mrna_high, -- AMP/GAIN with high protein (regardless of mRNA) SUM(CASE WHEN cna_gistic >= 1 AND protein_zscore > 2 THEN 1 ELSE 0 END) AS amp_and_protein_high, -- High mRNA with high protein (regardless of CNA) SUM(CASE WHEN mrna_zscore > 2 AND protein_zscore > 2 THEN 1 ELSE 0 END) AS mrna_and_protein_both_high, -- AMP/GAIN only (no high expression) SUM(CASE WHEN cna_gistic >= 1 AND mrna_zscore <= 2 AND protein_zscore <= 2 THEN 1 ELSE 0 END) AS amp_only_no_expr, -- High mRNA only (no CNA amp) SUM(CASE WHEN cna_gistic < 1 AND mrna_zscore > 2 AND protein_zscore <= 2 THEN 1 ELSE 0 END) AS mrna_high_no_cna_no_protein, -- Discordant patterns SUM(CASE WHEN cna_gistic >= 1 THEN 1 ELSE 0 END) AS total_amplified FROM with_all_data ) SELECT total_n, all_three_high, ROUND(100.0 * all_three_high / total_n, 1) AS pct_all_three_concordant, amp_and_mrna_high, ROUND(100.0 * amp_and_mrna_high / total_n, 1) AS pct_amp_with_mrna_high, amp_and_protein_high, ROUND(100.0 * amp_and_protein_high / total_n, 1) AS pct_amp_with_protein_high, mrna_and_protein_both_high, ROUND(100.0 * mrna_and_protein_both_high / total_n, 1) AS pct_mrna_protein_concordant, amp_only_no_expr, ROUND(100.0 * amp_only_no_expr / total_amplified, 1) AS pct_amp_without_overexpression, total_amplified, ROUND(100.0 * total_amplified / total_n, 1) AS pct_samples_amplified FROM concordance_calculations; ", "max_rows": 5 } ◀ result {"rows":[{"total_n":105,"all_three_high":7,"pct_all_three_concordant":6.7,"amp_and_mrna_high":24,"pct_amp_with_mrna_high":22.9,"amp_and_protein_high":7,"pct_amp_with_protein_high":6.7,"mrna_and_protein_both_high":7,"pct_mrna_protein_concordant":6.7,"amp_only_no_expr":20,"pct_amp_without_overexpression":45.5,"total_amplified":44,"pct_samples_amplified":41.9}]} ▶ clickhouse_run_select_query { "query": " -- ERBB2: Pearson correlation between CNA (GISTIC), mRNA, and protein -- TCGA BRCA PanCancer Atlas WITH erbb2_aligned AS ( SELECT sample_unique_id, MAX(CASE WHEN profile_type = 'gistic' THEN toInt32(alteration_value) END) AS cna_gistic, MAX(CASE WHEN profile_type = 'rna_seq_v2_mrna_median_Zscores' THEN toFloat64(alteration_value) END) AS mrna_zscore, MAX(CASE WHEN profile_type = 'protein_quantification_zscores' THEN toFloat64(alteration_value) END) AS protein_zscore FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018' AND hugo_gene_symbol = 'ERBB2' GROUP BY sample_unique_id ), with_all_data AS ( SELECT toFloat64(cna_gistic) AS x_cna, mrna_zscore AS y_mrna, protein_zscore AS z_protein FROM erbb2_aligned WHERE cna_gistic IS NOT NULL AND mrna_zscore IS NOT NULL AND protein_zscore IS NOT NULL ), stats AS ( SELECT COUNT(*) AS n, AVG(x_cna) AS mean_x, AVG(y_mrna) AS mean_y, AVG(z_protein) AS mean_z, SQRT(SUM((x_cna - AVG(x_cna) OVER ())*(x_cna - AVG(x_cna) OVER ())) / (COUNT(*) - 1)) AS sd_x, SQRT(SUM((y_mrna - AVG(y_mrna) OVER ())*(y_mrna - AVG(y_mrna) OVER ())) / (COUNT(*) - 1)) AS sd_y, SQRT(SUM((z_protein - AVG(z_protein) OVER ())*(z_protein - AVG(z_protein) OVER ())) / (COUNT(*) - 1)) AS sd_z, SUM((x_cna - AVG(x_cna) OVER ()) * (y_mrna - AVG(y_mrna) OVER ())) / (COUNT(*) - 1) AS cov_xy, SUM((x_cna - AVG(x_cna) OVER ()) * (z_protein - AVG(z_protein) OVER ())) / (COUNT(*) - 1) AS cov_xz, SUM((y_mrna - AVG(y_mrna) OVER ()) * (z_protein - AVG(z_protein) OVER ())) / (COUNT(*) - 1) AS cov_yz FROM with_all_data ) SELECT n, ROUND(mean_x, 2) AS mean_cna, ROUND(mean_y, 2) AS mean_mrna, ROUND(mean_z, 2) AS mean_protein, ROUND(cov_xy / (sd_x * sd_y), 3) AS pearson_cna_vs_mrna, ROUND(cov_xz / (sd_x * sd_z), 3) AS pearson_cna_vs_protein, ROUND(cov_yz / (sd_y * sd_z), 3) AS pearson_mrna_vs_protein FROM stats; ", "max_rows": 5 } ◀ result {"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 184\n Code: 184. DB::Exception: Window function AVG(x_cna) OVER () is found inside an aggregate function in query. (ILLEGAL_AGGREGATION) (version 26.4.1.2359 (official build))\n"} ▶ clickhouse_run_select_query { "query": " -- ERBB2: Correlations between CNA, mRNA, and protein -- TCGA BRCA PanCancer Atlas WITH erbb2_aligned AS ( SELECT toFloat64(MAX(CASE WHEN profile_type = 'gistic' THEN toInt32(alteration_value) END)) AS cna_gistic, MAX(CASE WHEN profile_type = 'rna_seq_v2_mrna_median_Zscores' THEN toFloat64(alteration_value) END) AS mrna_zscore, MAX(CASE WHEN profile_type = 'protein_quantification_zscores' THEN toFloat64(alteration_value) END) AS protein_zscore FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018' AND hugo_gene_symbol = 'ERBB2' GROUP BY sample_unique_id ), with_all_data AS ( SELECT * FROM erbb2_aligned WHERE cna_gistic IS NOT NULL AND mrna_zscore IS NOT NULL AND protein_zscore IS NOT NULL ) SELECT COUNT(*) AS n_samples, ROUND(corr(cna_gistic, mrna_zscore), 3) AS corr_cna_mrna, ROUND(corr(cna_gistic, protein_zscore), 3) AS corr_cna_protein, ROUND(corr(mrna_zscore, protein_zscore), 3) AS corr_mrna_protein, ROUND(AVG(cna_gistic), 2) AS mean_cna, ROUND(AVG(mrna_zscore), 2) AS mean_mrna, ROUND(AVG(protein_zscore), 2) AS mean_protein, ROUND(stddevPop(cna_gistic), 2) AS sd_cna, ROUND(stddevPop(mrna_zscore), 2) AS sd_mrna, ROUND(stddevPop(protein_zscore), 2) AS sd_protein FROM with_all_data; ", "max_rows": 5 } ◀ result {"rows":[{"n_samples":105,"corr_cna_mrna":0.573,"corr_cna_protein":0.695,"corr_mrna_protein":0.822,"mean_cna":0.32,"mean_mrna":5.55,"mean_protein":0,"sd_cna":1.08,"sd_mrna":15.6,"sd_protein":1}]} ▶ clickhouse_run_select_query { "query": " -- ERBB2 concordance in CPTAC Breast Cancer (proteogenomic study) -- This study has comprehensive proteomics and genomics WITH erbb2_aligned AS ( SELECT sample_unique_id, MAX(CASE WHEN profile_type = 'gistic' THEN toInt32(alteration_value) END) AS cna_gistic, MAX(CASE WHEN profile_type LIKE '%mrna%' OR profile_type LIKE '%expression%' THEN toFloat64(alteration_value) END) AS mrna_value, MAX(CASE WHEN profile_type = 'protein_quantification_zscores' THEN toFloat64(alteration_value) END) AS protein_zscore FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_cptac_2020' AND hugo_gene_symbol = 'ERBB2' GROUP BY sample_unique_id ), with_all_data AS ( SELECT * FROM erbb2_aligned WHERE cna_gistic IS NOT NULL AND mrna_value IS NOT NULL AND protein_zscore IS NOT NULL ) SELECT 'brca_cptac_2020' AS study, COUNT(*) AS n_samples_all_three, SUM(CASE WHEN cna_gistic >= 1 THEN 1 ELSE 0 END) AS n_amplified, ROUND(100.0 * SUM(CASE WHEN cna_gistic >= 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_amplified, SUM(CASE WHEN mrna_value > 0 THEN 1 ELSE 0 END) AS n_mrna_high, SUM(CASE WHEN protein_zscore > 2 THEN 1 ELSE 0 END) AS n_protein_high, SUM(CASE WHEN cna_gistic >= 1 AND protein_zscore > 2 THEN 1 ELSE 0 END) AS amp_and_protein_high, ROUND(corr(toFloat64(cna_gistic), protein_zscore), 3) AS corr_cna_protein, ROUND(corr(mrna_value, protein_zscore), 3) AS corr_mrna_protein FROM with_all_data; ", "max_rows": 5 } ◀ result {"rows":[{"study":"brca_cptac_2020","n_samples_all_three":0,"n_amplified":0,"pct_amplified":null,"n_mrna_high":0,"n_protein_high":0,"amp_and_protein_high":0}]} ▶ clickhouse_run_select_query { "query": " -- List all studies with adequate ERBB2 data coverage (CNA + mRNA + protein) SELECT DISTINCT cs.cancer_study_identifier, COUNT(DISTINCT CASE WHEN profile_type LIKE '%gistic%' OR profile_type LIKE '%cna%' THEN sample_unique_id ELSE NULL END) AS cna_samples, COUNT(DISTINCT CASE WHEN profile_type LIKE '%mrna%' OR profile_type LIKE '%expression%' THEN sample_unique_id ELSE NULL END) AS mrna_samples, COUNT(DISTINCT CASE WHEN profile_type LIKE '%protein%' OR profile_type LIKE '%rppa%' THEN sample_unique_id ELSE NULL END) AS protein_samples FROM genetic_alteration_derived gad JOIN cancer_study cs ON gad.cancer_study_identifier = cs.cancer_study_identifier WHERE cs.type_of_cancer_id IN ('brca', 'breast') AND gad.hugo_gene_symbol = 'ERBB2' GROUP BY cs.cancer_study_identifier HAVING cna_samples > 30 AND mrna_samples > 30 AND protein_samples > 30 ORDER BY cna_samples DESC; ", "max_rows": 20 } ◀ result {"rows":[{"cs.cancer_study_identifier":"brca_tcga","cna_samples":1080,"mrna_samples":1100,"protein_samples":892},{"cs.cancer_study_identifier":"brca_tcga_pan_can_atlas_2018","cna_samples":1070,"mrna_samples":1082,"protein_samples":876},{"cs.cancer_study_identifier":"brca_tcga_pub2015","cna_samples":816,"mrna_samples":817,"protein_samples":673},{"cs.cancer_study_identifier":"brca_tcga_pub","cna_samples":778,"mrna_samples":528,"protein_samples":408},{"cs.cancer_study_identifier":"brca_cptac_2020","cna_samples":122,"mrna_samples":122,"protein_samples":122},{"cs.cancer_study_identifier":"brca_dldccc_2022","cna_samples":48,"mrna_samples":52,"protein_samples":71}]} ▶ clickhouse_run_select_query { "query": " -- Compare ERBB2 concordance across multiple TCGA breast cancer datasets -- Focus on samples with all three data types WITH brca_tcga_leg AS ( SELECT 'TCGA BRCA (Legacy)' AS study, toFloat64(MAX(CASE WHEN profile_type LIKE '%gistic%' THEN toInt32(alteration_value) END)) AS cna, MAX(CASE WHEN profile_type LIKE '%linear_cna%' THEN toFloat64(alteration_value) END) AS log2_cna, MAX(CASE WHEN profile_type LIKE '%rppa_Zscores%' THEN toFloat64(alteration_value) END) AS protein_z FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_tcga' AND hugo_gene_symbol = 'ERBB2' GROUP BY sample_unique_id ), brca_tcga_pub AS ( SELECT 'TCGA BRCA (Nature 2012)' AS study, toFloat64(MAX(CASE WHEN profile_type LIKE '%gistic%' THEN toInt32(alteration_value) END)) AS cna, MAX(CASE WHEN profile_type LIKE '%linear_cna%' THEN toFloat64(alteration_value) END) AS log2_cna, MAX(CASE WHEN profile_type LIKE '%rppa_Zscores%' THEN toFloat64(alteration_value) END) AS protein_z FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_tcga_pub' AND hugo_gene_symbol = 'ERBB2' GROUP BY sample_unique_id ), brca_tcga_pub2015 AS ( SELECT 'TCGA BRCA (Cell 2015)' AS study, toFloat64(MAX(CASE WHEN profile_type LIKE '%gistic%' THEN toInt32(alteration_value) END)) AS cna, MAX(CASE WHEN profile_type LIKE '%linear_cna%' THEN toFloat64(alteration_value) END) AS log2_cna, MAX(CASE WHEN profile_type LIKE '%protein_quantification_zscores%' THEN toFloat64(alteration_value) END) AS protein_z FROM genetic_alteration_derived WHERE cancer_study_identifier = 'brca_tcga_pub2015' AND hugo_gene_symbol = 'ERBB2' GROUP BY sample_unique_id ) SELECT study, COUNT(*) AS n_with_all_three, SUM(CASE WHEN cna >= 1 THEN 1 ELSE 0 END) AS n_amp_gain, ROUND(100.0 * SUM(CASE WHEN cna >= 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_amp_gain, SUM(CASE WHEN protein_z > 2 THEN 1 ELSE 0 END) AS n_protein_high, ROUND(100.0 * SUM(CASE WHEN protein_z > 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_protein_high, SUM(CASE WHEN cna >= 1 AND protein_z > 2 THEN 1 ELSE 0 END) AS amp_and_protein_high, ROUND(100.0 * SUM(CASE WHEN cna >= 1 AND protein_z > 2 THEN 1 ELSE 0 END) / SUM(CASE WHEN cna >= 1 THEN 1 ELSE 0 END), 1) AS pct_concordant_given_amp FROM ( SELECT * FROM brca_tcga_leg WHERE cna IS NOT NULL AND protein_z IS NOT NULL UNION ALL SELECT * FROM brca_tcga_pub WHERE cna IS NOT NULL AND protein_z IS NOT NULL UNION ALL SELECT * FROM brca_tcga_pub2015 WHERE cna IS NOT NULL AND protein_z IS NOT NULL ) GROUP BY study ORDER BY n_with_all_three DESC; ", "max_rows": 10 } ◀ result {"rows":[{"study":"TCGA BRCA (Legacy)","n_with_all_three":828,"n_amp_gain":262,"pct_amp_gain":31.6,"n_protein_high":51,"pct_protein_high":6.2,"amp_and_protein_high":51,"pct_concordant_given_amp":19.5},{"study":"TCGA BRCA (Cell 2015)","n_with_all_three":672,"n_amp_gain":222,"pct_amp_gain":33,"n_protein_high":43,"pct_protein_high":6.4,"amp_and_protein_high":43,"pct_concordant_given_amp":19.4},{"study":"TCGA BRCA (Nature 2012)","n_with_all_three":383,"n_amp_gain":104,"pct_amp_gain":27.2,"n_protein_high":24,"pct_protein_high":6.3,"amp_and_protein_high":24,"pct_concordant_given_amp":23.1}]} ▶ resolve_and_route { "studyIds": [ "brca_tcga_pan_can_atlas_2018" ] } ◀ result {"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"brca_tcga_pan_can_atlas_2018","name":"Breast Invasive Carcinoma (TCGA, PanCancer Atlas)","sampleCount":1084,"studyViewUrl":"https://www.cbioportal.org/study?id=brca_tcga_pan_can_atlas_2018","metadata":{"clinicalAttributeIds":["AGE","AJCC_PATHOLOGIC_TUMOR_STAGE","AJCC_STAGING_EDITION","ANEUPLOIDY_SCORE","BUFFA_HYPOXIA_SCORE","CANCER_TYPE","CANCER_TYPE_ACRONYM","CANCER_TYPE_DETAILED","DAYS_LAST_FOLLOWUP","DAYS_TO_BIRTH","DAYS_TO_INITIAL_PATHOLOGIC_DIAGNOSIS","DFS_MONTHS","DFS_STATUS","DSS_MONTHS","DSS_STATUS","ETHNICITY","FORM_COMPLETION_DATE","FRACTION_GENOME_ALTERED","GENETIC_ANCESTRY_LABEL","GRADE","HISTORY_NEOADJUVANT_TRTYN","ICD_10","ICD_O_3_HISTOLOGY","ICD_O_3_SITE","INFORMED_CONSENT_VERIFIED","IN_PANCANPATHWAYS_FREEZE","MSI_SCORE_MANTIS","MSI_SENSOR_SCORE","MUTATION_COUNT","NEW_TUMOR_EVENT_AFTER_INITIAL_TREATMENT","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","OTHER_PATIENT_ID","PATH_M_STAGE","PATH_N_STAGE","PATH_T_STAGE","PERSON_NEOPLASM_CANCER_STATUS","PFS_MONTHS","PFS_STATUS","PRIMARY_LYMPH_NODE_PRESENTATION_ASSESSMENT","PRIOR_DX","RACE","RADIATION_THERAPY","RAGNUM_HYPOXIA_SCORE","SAMPLE_COUNT","SAMPLE_TYPE","SEX","SOMATIC_STATUS","SUBTYPE","TBL_SCORE","TISSUE_PROSPECTIVE_COLLECTION_INDICATOR","TISSUE_RETROSPECTIVE_COLLECTION_INDICATOR","TISSUE_SOURCE_SITE","TISSUE_SOURCE_SITE_CODE","TMB_NONSYNONYMOUS","TUMOR_TISSUE_SITE","TUMOR_TYPE","WEIGHT","WINTER_HYPOXIA_SCORE"],"molecularProfileIds":["brca_tcga_pan_can_atlas_2018_gistic","brca_tcga_pan_can_atlas_2018_log2CNA","brca_tcga_pan_can_atlas_2018_mutations","brca_tcga_pan_can_atlas_2018_protein_quantification","brca_tcga_pan_can_atlas_2018_protein_quantification_zscores","brca_tcga_pan_can_atlas_2018_rna_seq_v2_mrna","brca_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","brca_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_Zscores","brca_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_ref_normal_Zscores","brca_tcga_pan_can_atlas_2018_rppa","brca_tcga_pan_can_atlas_2018_rppa_Zscores","brca_tcga_pan_can_atlas_2018_structural_variants"],"genericAssayProfiles":["brca_tcga_pan_can_atlas_2018_armlevel_cna","brca_tcga_pan_can_atlas_2018_genetic_ancestry","brca_tcga_pan_can_atlas_2018_methylation_hm27_hm450_merge","brca_tcga_pan_can_atlas_2018_methylation_hm450","brca_tcga_pan_can_atlas_2018_phosphoprotein_quantification"],"heatmapProfileIds":["brca_tcga_pan_can_atlas_2018_protein_quantification_zscores","brca_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","brca_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_Zscores","brca_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_ref_normal_Zscores","brca_tcga_pan_can_atlas_2018_rppa_Zscores"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations","mrna","protein","generic_assay_armlevel_cna","generic_assay_genetic_anc … (83 more chars) ▶ navigate_to_results_view { "studyIds": [ "brca_tcga_pan_can_atlas_2018" ], "genes": [ "ERBB2" ], "tab": "plots", "plotsHorzSelection": { "selectedGeneOption": "ERBB2", "dataType": "MRNA_EXPRESSION", "logScale": "false" }, "plotsVertSelection": { "selectedGeneOption": "ERBB2", "dataType": "PROTEIN_LEVEL", "logScale": "false" } } ◀ result {"success":true,"message":"Navigating to https://www.cbioportal.org/results/plots?cancer_study_list=brca_tcga_pan_can_atlas_2018&gene_list=ERBB2&case_set_id=brca_tcga_pan_can_atlas_2018_all&plots_horz_selection=%7B%22selectedGeneOption%22%3A%222064%22%2C%22dataType%22%3A%22MRNA_EXPRESSION%22%2C%22logScale%22%3A%22false%22%7D&plots_vert_selection=%7B%22selectedGeneOption%22%3A%222064%22%2C%22dataType%22%3A%22PROTEIN_LEVEL%22%2C%22logScale%22%3A%22false%22%7D","url":"https://www.cbioportal.org/results/plots?cancer_study_list=brca_tcga_pan_can_atlas_2018&gene_list=ERBB2&case_set_id=brca_tcga_pan_can_atlas_2018_all&plots_horz_selection=%7B%22selectedGeneOption%22%3A%222064%22%2C%22dataType%22%3A%22MRNA_EXPRESSION%22%2C%22logScale%22%3A%22false%22%7D&plots_vert_selection=%7B%22selectedGeneOption%22%3A%222064%22%2C%22dataType%22%3A%22PROTEIN_LEVEL%22%2C%22logScale%22%3A%22false%22%7D","data":{"studyIds":["brca_tcga_pan_can_atlas_2018"],"studies":[{"studyId":"brca_tcga_pan_can_atlas_2018","name":"Breast Invasive Carcinoma (TCGA, PanCancer Atlas)","sampleCount":1084}],"genes":["ERBB2"],"caseSetId":"brca_tcga_pan_can_atlas_2018_all","pageDescription":"Scatter/box plots: ERBB2 protein expression (y-axis) by ERBB2 mRNA expression (x-axis)."}} ▶ navigate_to_results_view { "studyIds": [ "brca_tcga_pan_can_atlas_2018" ], "genes": [ "ERBB2" ], "tab": "oncoprint", "oncoprintHeatmapTracks": [ { "molecularProfileId": "brca_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores", "entities": [ "ERBB2" ] }, { "molecularProfileId": "brca_tcga_pan_can_atlas_2018_protein_quantification_zscores", "entities": [ "ERBB2" ] } ] } ◀ result {"success":true,"message":"Navigating to https://www.cbioportal.org/results/oncoprint?cancer_study_list=brca_tcga_pan_can_atlas_2018&gene_list=ERBB2&case_set_id=brca_tcga_pan_can_atlas_2018_all&heatmap_track_groups=brca_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores%2CERBB2%3Bbrca_tcga_pan_can_atlas_2018_protein_quantification_zscores%2CERBB2","url":"https://www.cbioportal.org/results/oncoprint?cancer_study_list=brca_tcga_pan_can_atlas_2018&gene_list=ERBB2&case_set_id=brca_tcga_pan_can_atlas_2018_all&heatmap_track_groups=brca_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores%2CERBB2%3Bbrca_tcga_pan_can_atlas_2018_protein_quantification_zscores%2CERBB2","data":{"studyIds":["brca_tcga_pan_can_atlas_2018"],"studies":[{"studyId":"brca_tcga_pan_can_atlas_2018","name":"Breast Invasive Carcinoma (TCGA, PanCancer Atlas)","sampleCount":1084}],"genes":["ERBB2"],"caseSetId":"brca_tcga_pan_can_atlas_2018_all","oncoprintHeatmapTracks":[{"molecularProfileId":"brca_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","entities":["ERBB2"]},{"molecularProfileId":"brca_tcga_pan_can_atlas_2018_protein_quantification_zscores","entities":["ERBB2"]}],"pageDescription":"Visual summary of alterations in queried genes across samples."}} ═ answer (success) ## Summary Here is the **ERBB2 concordance analysis** between copy number amplification, mRNA overexpression, and protein overexpression in breast cancer: --- ### **Key Findings (TCGA BRCA PanCancer Atlas, n=1,084 samples)** **Data Coverage:** - 1,070 samples with CNA data (98.7%) - 1,082 samples with mRNA data (99.8%) - 876 samples with protein data (80.7%) - **105 samples with complete three-modality data** (9.7% of cohort) --- ### **Concordance Summary (105 samples with all three data types)** | Alteration Pattern | Count | % of All | |---|---|---| | **All three normal (concordant)** | 61 | 58.1% | | **Amp/Gain + mRNA HIGH + Protein HIGH** | 7 | 6.7% | | **Amp/Gain + mRNA HIGH only** | 17 | 16.2% | | **Amp/Gain but no overexpression** | 20 | 19.1% | **Key Insight:** Only **6.7%** of samples show complete concordance (CNA amplification + mRNA overexpression + protein overexpression). **41.9%** have CNA amplification, but only **54.5%** of amplified cases show at least mRNA overexpression. --- ### **Correlation Analysis (105 samples with all three measures)** | Measure | Pearson r | |---|---| | **CNA ↔ mRNA** | **0.573** (moderate-strong) | | **CNA ↔ Protein** | **0.695** (strong) | | **mRNA ↔ Protein** | **0.822** (very strong) | **Interpretation:** - mRNA and protein levels are highly concordant (r = 0.82) - CNA correlates better with protein (r = 0.70) than mRNA (r = 0.57) - CNA alone is an imperfect predictor of expression at both mRNA and protein levels --- ### **Expression by CNA Status (n = 1,070 with CNA)** | CNA Status | n | Mean mRNA Z-score | Mean Protein Z-score | |---|---|---|---| | **Amplification (2)** | 123 | **+27.2** | **+1.54** | | **Gain (1)** | 206 | **+1.9** | **–0.05** | | **Diploid (0)** | 481 | **0** | **–0.36** | | **Loss (–1)** | 260 | **–0.97** | **–0.61** | **Key**: Only 22.9% of amplified samples have mRNA z-score > 2 (high). For protein, only 6.7% exceed z-score > 2 (protein profiling is sparse in RPPA cohort). --- ### **Consistency Across Multiple TCGA Datasets** | Study | n (all three) | Amp/Gain | Protein HIGH | Concordant* | |---|---|---|---|---| | **TCGA BRCA Legacy** | 828 | 31.6% | 6.2% | 19.5%** | | **TCGA BRCA (Cell 2015)** | 672 | 33.0% | 6.4% | 19.4%** | | **TCGA BRCA (Nature 2012)** | 383 | 27.2% | 6.3% | 23.1%** | *Protein high among amplified samples. **Confirms poor CNA-protein concordance across cohorts.** --- ### **Visualize This Data** - **[Scatter plot: mRNA vs Protein Expression](https://www.cbioportal.org/results/plots?cancer_study_list=brca_tcga_pan_can_atlas_2018&gene_list=ERBB2&case_set_id=brca_tcga_pan_can_atlas_2018_all&plots_horz_selection=%7B%22selectedGeneOption%22%3A%222064%22%2C%22dataType%22%3A%22MRNA_EXPRESSION%22%2C%22logScale%22%3A%22false%22%7D&plots_vert_selection=%7B%22selectedGeneOption%22%3A%222064%22%2C%22dataType%22%3A%22PROTEIN_LEVEL%22%2C%22logScale%22%3A%22false%22%7D)** — Shows the strong mRNA–protein correlation - **[OncoPrint with heatmaps](https://www.cbioportal.org/results/oncoprint?cancer_study_list=brca_tcga_pan_can_atlas_2018&gene_list=ERBB2&case_set_id=brca_tcga_pan_can_atlas_2018_all&heatmap_track_groups=brca_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores%2CERBB2%3Bbrca_tcga_pan_can_atlas_2018_protein_quantification_zscores%2CERBB2)** — See CNA amplifications with mRNA and protein tracks --- ### **Clinical Implication** **Discordance between CNA and protein is substantial.** Among amplified samples, ~45% lack protein overexpression. This suggests: - Post-transcriptional regulation (miRNA, protein stability, degradation) - Post-translational modifications affecting detection - Cell-line-specific effects in cell-based assays (RPPA uses cell extracts) - Tumor heterogeneity and sampling variation **HER2 status in breast cancer (for treatment eligibility) typically relies on IHC/FISH protein/CNA, not mRNA.** The modest CNA–protein correlation underscores why HER2 testing combines multiple modalities rather than CNA alone.