Exclude results from query with specific text in CLOB field-CodePudding

I am looking to exclude records with the words 'rich text' in the NRS.FULL_TEXT (CLOB data type) field. I am working in oracle sql.

I tried using the NOT LIKE function and I received no results. Also, I tried the following code but received an error: ORA-00932: inconsistent datatypes: expected - got CLOB

    SELECT 
        PT.PATIENT_MRN AS MRN
        ,ORD.PATIENT_ID
        ,ORD.ORDER_DATE
        ,ORD.ORDER_TYPE
        ,ORD.ORDER_PROC
        ,ORD.SPECIMEN_SOURCE
        ,ORS.COMPONENT_NAME
        ,ORS.RESULT_TEXT
        ,NRS.FULL_TEXT
    FROM RDM.PATIENT                PT
    LEFT JOIN RDM.ORDERS            ORD ON PT.PATIENT_ID = ORD.PATIENT_ID 
    LEFT JOIN RDM.ORDER_RESULT      ORS ON ORS.ORDER_ID = ORD.ORDER_ID
    LEFT JOIN RDM.NOTE_RSLT         NRS ON ORD.ORDER_ID = NRS.ORDER_ID
    WHERE ORD.ORDER_DATE BETWEEN '01-JUL-2021 12:00:00 AM' AND '30-JUN-2022 11:59:59 PM'
    AND (FLOOR(MONTHS_BETWEEN(SYSDATE, PT.BIRTH_DATE)/12)>18)   
    AND PT.PATIENT_MRN NOT IN (
    SELECT NRS.FULL_TEXT FROM RDM.NOTE_RSLT WHERE NRS.FULL_TEXT LIKE '%rich text%')

CodePudding user response：

We can use normal SQL functions with CLOB data when the CLOB is <= 4000, i.e. small enough to treat as VARCHAR2. But when we work with larger CLOBs it is best to use functions from the built-in Oracle DBMS_LOB package.

    SELECT 
        PT.PATIENT_MRN AS MRN
        ,ORD.PATIENT_ID
        ,ORD.ORDER_DATE
        ,ORD.ORDER_TYPE
        ,ORD.ORDER_PROC
        ,ORD.SPECIMEN_SOURCE
        ,ORS.COMPONENT_NAME
        ,ORS.RESULT_TEXT
        ,NRS.FULL_TEXT
    FROM RDM.PATIENT                PT
    LEFT JOIN RDM.ORDERS            ORD ON PT.PATIENT_ID = ORD.PATIENT_ID 
    LEFT JOIN RDM.ORDER_RESULT      ORS ON ORS.ORDER_ID = ORD.ORDER_ID
    LEFT JOIN RDM.NOTE_RSLT         NRS ON ORD.ORDER_ID = NRS.ORDER_ID
    WHERE ORD.ORDER_DATE BETWEEN '01-JUL-2021 12:00:00 AM' AND '30-JUN-2022 11:59:59 PM'
    AND (FLOOR(MONTHS_BETWEEN(SYSDATE, PT.BIRTH_DATE)/12)>18)   
    AND PT.PATIENT_MRN NOT IN (
        SELECT NRS.FULL_TEXT 
        FROM RDM.NOTE_RSLT 
        WHERE dbms_lob.instr(NRS.FULL_TEXT, '%rich text%') > 0
        )

CodePudding user response：

Thank you all for your responses. I was able to resolve this issue by using the dbms_lob.substr function as follows:

SELECT 
    PT.PATIENT_MRN AS MRN
    ,ORD.PATIENT_ID AS PAT_ID
    ,ORD.ORDER_DATE AS ORD_DATE
    ,ORD.ORDER_TYPE AS ORD_TYPE
    ,ORD.ORDER_PROC AS ORD_PROC
    ,ORD.SPECIMEN_SOURCE AS SPECIMEN_SRC
    ,ORS.COMPONENT_NAME AS NOTE_TYPE
    ,ORS.RESULT_TEXT AS FINAL_DX
    ,DBMS_LOB.SUBSTR(NRS.FULL_TEXT, 4000, 1) AS NOTES 
FROM RDM.PATIENT                PT
LEFT JOIN RDM.ORDERS            ORD ON PT.PATIENT_ID = ORD.PATIENT_ID 
LEFT JOIN RDM.ORDER_RESULT      ORS ON ORS.ORDER_ID = ORD.ORDER_ID
LEFT JOIN RDM.NOTE_RSLT         NRS ON ORD.ORDER_ID = NRS.ORDER_ID
WHERE ORD.ORDER_DATE BETWEEN '01-JUL-2021 12:00:00 AM' AND '30-JUN-2022 11:59:59 
 PM'
AND (FLOOR(MONTHS_BETWEEN(SYSDATE, PT.BIRTH_DATE)/12)>18)

Afterwards, I was able to search for a string using WHERE...NOT IN. The CLOB field was greater that 4000 characters so this worked well for me.