Fledgling Oracle SQL writer here, so all education and resources are appreciated...
Say I would like to find people who are pregnant by way of the International Classification of Diseases 10th edition (aka ICD10 codes). There are 1,776 potential diagnosis that fit our search
(45 Prefixes match, each with multiple suffixes like 'Z34.01' for Encounter for supervision of normal first pregnancy, first trimester or O99.712 Diseases of the skin & tissue complicating pregnancy, second trimester, etc.) .
For our search I am returning
Date of Diagnosis, ICD10, from a single Diagnosis table where my Patient ID exists (and the key is a patient encounter distinct key)
SELECT
PATIENT_ID, DATE, ICD10
FROM DX WHERE ICD10 LIKE ('Z34.%')
OR ICD10 LIKE ('O99.%' )
QUESTIONs:
Is is efficient to have it run 45 LIKE clauses against all patients encounters?
Is it efficient to create reference table of the codes I need and have all combinations ran against a JOIN to my reference table?
Is there a better way to tackle this in Oracle SQL Developer 22.2 ?
- Thank you in advance for all advice and ideas!