Filter one field by another field of the same table (1 Viewer)

Hello,

I would like to filter [gene_panel] fields by [gene] field of the same table.

The [gene] field consists on a single gene name (e.g KRAS) and the [gene_panel] field consists of a comma delimited string of genes (KRAS,NRAS,KIT). I would like a query to return records where [gene_panel] contains [gene]. For, example, “KRAS,NRAS,KIT” contains “KRAS”, therefore, this record should be returned.

I’ve tried the following query but it’s returning all records, even when gene_panel doesn’t contain gene.

Here is the SQL:


Thanks, Mila

1. Your table structure is improper. Discrete pieces of data should be stored discretely. You shouldn't cram a bunch of information into one field seperated by commas. That means your genepool field should instead become its own table with each value of genepool in its own record.

2. Instead of LIKE in your query, check out the InStr function (https://www.techonthenet.com/oracle/functions/instr.php).

Expression like this: instr(gene_panle, panel) > 0 that will return True or False?

Yes, that will return true or false.

You would be far better off redesigning your table structure before you go much further!

I would suggest something along these lines:-
A new table for storing gene_panel

tblGenePanel
Fields:-
GenePanelID (auto number)( probably not necessary but I usually add an ID field on initial construction)
GenePanelPathway (integer)

tblPathways
11 - EGFR
12 - BRAF
13 - KRAS
14 - PIK3CA3
15 - AKT1
60 - HD200_QC

tblGenePanelPath
GenePanelPathID (auto number)( probably not necessary but I usually add an ID field on initial construction)
GenePanelPathGeneRef (As is would be:- PIK3CA3, FGFR3, APC, NRAS, but might/would? be better with a lookup table)
GenePanelPathGenePath (Long) (This is where the lookup table info goes)

So tblGenePanelPath would look something like this:-

GenePanelPathID - GenePanelPathGeneRef - GenePanelPathGenePath
------- 1 ------------- PIK3CA3 -------- 11 from lookup table you get (EGFR)
------- 2 ------------- PIK3CA3 -------- 12 from lookup table you get (BRAF)
------- 3 ------------- PIK3CA3 -------- 13 from lookup table you get (KRAS)
------- 4 ------------- PIK3CA3 -------- 14 from lookup table you get (PIK3CA3)
------- 5 ------------- PIK3CA3 -------- 15 from lookup table you get (AKT1)

You could add another field to this table which would identify the Gene path and the Gene type and then you could store these values (again really want to be lookups)

GenePanelPathID - GenePanelPathGeneRef - TumorGene - GenePanelPathGenePath
------- 1 ------------- PIK3CA3 ----------------- Gene ----- 11 from lookup table you get (EGFR)
------- 2 ------------- PIK3CA3 ----------------- Gene ----- 12 from lookup table you get (BRAF)
------- 3 ------------- PIK3CA3 ----------------- Gene ----- 13 from lookup table you get (KRAS)
------- 4 ------------- PIK3CA3 ----------------- Gene ----- 14 from lookup table you get (PIK3CA3)
------- 5 ------------- PIK3CA3 ----------------- Gene ----- 15 from lookup table you get (AKT1)
------- 6 ------------- PIK3CA3 ----------------- Tumor ---- 60 from lookup table you get (HD200_QC)