CODEWITHSUNDEEP
oracle-databasesubquery
Lawrence Block
Lawrence Block

Reputation: 73

subquery efficiencies/errors in Oracle

Attempting to use a subquery to return a value if there is a positive value returned-however the subquery either errors (single-row subquery returns more than one row) if I attempt to group by x

the background is that I am searching through free text notes looking for '%home%' -if there is any note anywhere (that matches the parameters) then I want 1 returned as a final value for each patient encounter-

I hope this makes sense.

I have also included the section simply as left joins in the larger query-

left join  HNO_INFO on pat_enc_hsp.pat_enc_csn_id=HNO_INFO.pat_enc_csn_id
left join HNO_NOTE_TEXT  ON HNO_INFO.note_id = HNO_NOTE_TEXT.note_id and  LOWER(note_text) like '%home%' and IP_NOTE_TYPE_C in ('35','100003','4','161100013','161100003')

However this results in multiple lines or if I use an inner join it will exclude encounters that still must be included. I can get around this by using by using a row_number() function if need be and re-order accordingly-however looking for the more efficient way in terms of computational speed

TIA

Part of the code-2nd section is the issue- 

select distinct CESR.PAT_MRN_ID,CESR.PREG_OUTCOME_DATE,HSP_LD_MOM_CHILD.pat_enc_csn_id,HSP_LD_MOM_CHILD.CHILD_ENC_CSN_ID,
pat_enc_hsp.HOSP_ADMSN_TIME,pat_enc_hsp.HOSP_DISCH_TIME,

(select sum(case when LOWER(note_text) like '%home%' then 1 else 0 end) from 
        pat_enc_hsp inner join HNO_INFO on pat_enc_hsp.pat_enc_csn_id=HNO_INFO.pat_enc_csn_id
inner join HNO_NOTE_TEXT  ON HNO_INFO.note_id = HNO_NOTE_TEXT.note_id and  LOWER(note_text) like '%home%' 
and IP_NOTE_TYPE_C in ('35','100003','4','161100013','161100003')
group by pat_enc_hsp.pat_enc_csn_id) as LD_NOTE

from  x.CESR_COHORT CESR inner join patient A on CESR.pat_mrn_id=a.pat_mrn_id
                        left join pat_enc_hsp on a.pat_id=pat_enc_hsp.pat_id and CESR.PREG_OUTCOME_DATE between trunc(pat_enc_hsp.HOSP_ADMSN_TIME) and trunc(pat_enc_hsp.HOSP_DISCH_TIME) 
                        left join HSP_LD_MOM_CHILD on pat_enc_hsp.pat_enc_csn_id=HSP_LD_MOM_CHILD.pat_enc_csn_id

Upvotes: 0

Views: 36

Answers (1)

kfinity
kfinity

Reputation: 9091

Oh - your problem is that your subquery isn't correlated with your main query - you have a pat_enc_hsp in there, but nothing in there says to match its columns with pat_enc_hsp in your main outer query. So your subquery is querying EVERYONE, not just a single patient. If you used column aliases for each table, it would be more obvious.

Try removing it, something like this instead:

SELECT DISTINCT
       CESR.PAT_MRN_ID,
       CESR.PREG_OUTCOME_DATE,
       HSP_LD_MOM_CHILD.pat_enc_csn_id,
       HSP_LD_MOM_CHILD.CHILD_ENC_CSN_ID,
       pat_enc_hsp.HOSP_ADMSN_TIME,
       pat_enc_hsp.HOSP_DISCH_TIME,
       (  SELECT SUM (
                     CASE
                         WHEN LOWER (note_text) LIKE '%home%' THEN 1
                         ELSE 0
                     END)
            FROM HNO_INFO -- no pat_enc_hsp in this subquery
                 INNER JOIN HNO_NOTE_TEXT
                     ON     HNO_INFO.note_id = HNO_NOTE_TEXT.note_id
                        AND LOWER (note_text) LIKE '%home%'
                        AND IP_NOTE_TYPE_C IN ('35',
                                               '100003',
                                               '4',
                                               '161100013',
                                               '161100003')
            WHERE pat_enc_hsp.pat_enc_csn_id = HNO_INFO.pat_enc_csn_id) -- references the outer query
           AS LD_NOTE
  FROM x.CESR_COHORT  CESR
       INNER JOIN patient A ON CESR.pat_mrn_id = a.pat_mrn_id
       LEFT JOIN pat_enc_hsp
           ON     a.pat_id = pat_enc_hsp.pat_id
              AND CESR.PREG_OUTCOME_DATE BETWEEN TRUNC (
                                                     pat_enc_hsp.HOSP_ADMSN_TIME)
                                             AND TRUNC (
                                                     pat_enc_hsp.HOSP_DISCH_TIME)
       LEFT JOIN HSP_LD_MOM_CHILD
           ON pat_enc_hsp.pat_enc_csn_id = HSP_LD_MOM_CHILD.pat_enc_csn_id

Upvotes: 1

Related Questions