Skip to content

Instantly share code, notes, and snippets.

@cessationoftime

cessationoftime/queryOracle.md

Forked from chris-vanvranken/queryOracle.md
Last active August 29, 2015 14:20
Show Gist options
  • Save cessationoftime/09aa2c32c8525fccb68b to your computer and use it in GitHub Desktop.
Save cessationoftime/09aa2c32c8525fccb68b to your computer and use it in GitHub Desktop.
Download ZIP
Raw
queryOracle.md

SQL Developer Manual

Problems unique to querying Oracle from Access

Syntax Error in FROM clause, invalid use of vertical bars in query expression

Oracle queries can fail for no good reason when running in Access.

When using || to concatenate, or at other times.

Oracle queries are more likely to work in access if you enclose your query, so that it is actually a subquery.

Select * FROM (
  -- SELECT My || concatenating || query
) X

Common problems

When converting a Date value to the name of the month the date lands on using TO_CHAR(datevalue,'MONTH'). The values returned by the TO_CHAR function will always contain 9 characters. So if you are looking to match 'JANUARY', you actually need to match 'JANUARY ' with 2 extra spaces at the end.

--if MONTH was generated with TO_CHAR(datevalue,'MONTH')
SELECT * FROM X WHERE MONTH = 'JANUARY'
-- should be
SELECT * FROM X WHERE MONTH = 'JANUARY  '

Syntax for Querying with Oracle

Substitution Variables

this will cause SqlDeveloper to ask you for the variables (BeginRange,EndRange), similar to what Access does.

where "ORDER#" BETWEEN &BeginRange AND &EndRange

Limit the query to the top 1000

SELECT * FROM (
--the query we want to limit
SELECT * FROM ORDERS

) WHERE rownum < 1000

Querying the Order# column (special characters in columnNames)

-- columnName is case-sentitive when it is in quotes
SELECT O."ORDER#" FROM ORDERS O

Replacement with Regex

REGEXP_REPLACE('my text','!*[^!]*(!CALLED [^!]*)','\1')
REGEXP_REPLACE('my text','.*','',1,0,'n')  -- allow . to match newline characters

Splitting a comma delimited string into multiple rows

http://nuijten.blogspot.com/2011/08/splitting-comma-delimited-string-regexp.html

 with test as
   (
   select 1 id, 'joey,anthony,marvin' str from dual union all
   select 5 id, 'tony,glenn' str from dual union all
   select 8 id, 'john' str from dual
   )
   select id
        , str
        , regexp_substr (str, '[^,]+', 1, rn) split
     from test
     cross
     join (select rownum rn
             from (select max (length (regexp_replace (str, '[^,]+'))) + 1 mx
                     from test
                  )
          connect by level <= mx
          )
    where regexp_substr (str, '[^,]+', 1, rn) is not null
    order by id
   ;

Removing spaces from text (ex: the messages table), that are not recognized as space '\s' characters

Some spaces in text are not spaces at all and cannot be identified by REGEX and '\s', rather they are unused field characters, AKA the NULL character. They can be removed like so:

REPLACE('my text', CHR(0), '')

Group Concatenation in a specific order

LISTAGG(Spec_M.TEXT, '') WITHIN GROUP (ORDER BY Spec.AA_ID, Spec_M.ASPEC_MES_SORT ASC)
--
--
group by ----

Concatenation

concatenation is best done using the double pipe ||

--LastName, FirstName
P.LAST_NAME || ', ' || P.FIRST_NAME as Name

Concatenating Newlines

--LastName\r\nFirstName
P.LAST_NAME || chr(13) || chr(10) || P.FIRST_NAME as Name

SELECT X IF NULL

NVL(testColumnIsNull,'replacementIfNull')
NVL2(testColumnIsNull,'useIfNotNull','useIfNull')

SELECT CAUSE: IF Column=0 THEN 'String1' ELSE IF Column=1 THEN 'String2'

CASE WHEN brstat=0 THEN 'Active' ELSE 'Inactive' END as Status
SELECT ARE_BILLRULES.BRTSTCODE as TestId, ARE_BILLRULES.BRCPTCODE as CptCode,
ARE_BILLRULES.BREXPDT as ExpDate, brchargecode as ChargeCode,
CASE WHEN brstat=0 THEN 'Active' ELSE 'Inactive' END as Status
FROM ARE_BILLRULES

Alternate way to use SubQueries

WITH RL AS (
   SELECT TEST_RESULTS."RESULT" AS RES FROM TEST_RESULTS
)

SELECT RES FROM RL

How to merge a Date column with a Time Column

The Soft database often uses separate columns to store Date and Time, this can often cause one some trouble. The Oracle Date datatype actually stores both Date and Time information. So Date and Time columns may be merged into one Date column, and then calculations can be done on the new Date column. Below shows how to merge the columns into a new column and convert the resulting column to a String for display, or leave it as a Date to perform calculations on.

select *

NVL2(tr.date_verified,TO_CHAR(TO_DATE(TO_CHAR(tr.date_verified,'yyyy-mm-dd') || TO_CHAR(tr.time_verified,'HH24:MI'),'yyyy-mm-ddHH24:MI'),'yyyy-mm-dd HH24:MI'),NULL) DateTime_Verified_AsString,

NVL2(tr.date_verified,TO_DATE(TO_CHAR(tr.date_verified,'yyyy-mm-dd') || TO_CHAR(tr.time_verified,'HH24:MI'),'yyyy-mm-ddHH24:MI'),NULL) DateTime_Verified_AsDateTime

FROM TEST_RESULTS

We can then use the merged column to get the difference between specimen receive time and result verified time in minutes. To do so, take the verified datetime and subtract the received datetime to get the difference in days. Then multiply by 24 and again by 60 to get the result in minutes. Then round the result to get a clean number.

 ROUND((NVL2(tr.date_verified,TO_DATE(TO_CHAR(tr.date_verified,'yyyy-mm-dd') || TO_CHAR(tr.time_verified,'HH24:MI'),'yyyy-mm-ddHH24:MI'),NULL) - NVL2(sp.date_receive,TO_DATE(TO_CHAR(sp.date_receive,'yyyy-mm-dd') || TO_CHAR(sp.time_receive,'HH24:MI'),'yyyy-mm-ddHH24:MI'),NULL))*24*60) MinutesDifference,

How to calculate difference from two dates to get age.

--Subtract the date to get number of days, divide by 365 to convert to years.  Then truncate to drop the decimal from the years.
TRUNC(((ORDERS.ORDERED_DATE)-(PATIENTS.DATE_OF_BIRTH))/365,0) AS AGE,

How to do a pivot

If we have 3 rows, one for each of the TEST_ID's 'VID25', 'VIDD2' and 'VIDD3' and their results. Then we can pivot to merge them into one row such that there are 3 new columns representing each TEST_ID and their result values. In this case We use the MAX aggregate function because PIVOT expects an aggregate function to be used. Other aggregate functions could be used here.

    SELECT * FROM (SELECT "RESULT" RES, TR.TEST_ID FROM ORDERS O JOIN TEST_RESULTS TR on O.AA_ID = TR.ACT_ATEST WHERE O."ORDER#" BETWEEN 'B1090000' AND 'B1130000' ) RL
    PIVOT (
      MAX(RES) FOR TEST_ID IN ('CA' AS Calcium, 'MG' AS Magnesium, 'GLU' AS Glucose)
    )

Determine if a String is Numeric and can be cast to an Int or Double

--char
select DECODE( TRANSLATE('12.345','0123456789',' '), NULL, 'number','char') test from dual
--number
select DECODE( TRANSLATE('12345','0123456789',' '), NULL, 'number','char') test from dual
 
--number
select DECODE( TRANSLATE('12.345','0123456789.',' '), NULL, 'number','char') test from dual
--number
select DECODE( TRANSLATE('12345','0123456789.',' '), NULL, 'number','char') test from dual

Reformatting a DateTime with the TO_CHAR function

TO_CHAR function

TO_CHAR(TEST_DATE, 'DD/MM') -- format to display day/month

Query to get the Tags on an Order from the messages table

WITH
OrdersQuery AS (
  SELECT AA_ID, ORDERS."ORDER#", PLAB_ACT, Priority FROM ORDERS WHERE ORDERS."ORDER#" = 'B1113604'
) 

,MessagesQuery As (
  SELECT OQ.AA_ID
  , OQ."ORDER#", OQ.PLAB_ACT, OQ.Priority
  ,M."TYPE" MessageType
  ,LISTAGG(REPLACE(REPLACE(REPLACE(M.Text,CHR(0),''), chr(13), ' '),chr(10), ' '), chr(13) || chr(10)) WITHIN GROUP (ORDER BY ACT_MES_SORT) TEXT
  FROM OrdersQuery OQ
  JOIN MESSAGES M ON OQ.AA_ID = M.ACT_MES
  WHERE M."TYPE" = 'T'
  group by OQ.AA_ID, OQ."ORDER#", OQ.PLAB_ACT, OQ.Priority, M."TYPE"
)

 SELECT * FROM MessagesQuery

Unpivoted (Normalized test columns) Version of the Orders table

SELECT O.*, substr(TEST_PREFIX, testIndex + 1, 1) TEST_PREFIX_OF_TESTINDEX
FROM   ORDERS
UNPIVOT (testOrders FOR testIndex IN (TEST0 AS '0', TEST1 AS '1', TEST2 AS '2', TEST3 AS '3', TEST4 AS '4', TEST5 AS '5', TEST6 AS '6', TEST7 AS '7', TEST8 AS '8', TEST9 AS '9', TEST10 AS '10', TEST11 AS '11', TEST12 AS '12', TEST13 AS '13', TEST14 AS '14', TEST15 AS '15', TEST16 AS '16', TEST17 AS '17', TEST18 AS '18', TEST19 AS '19', AOTESTS_20 AS '20', AOTESTS_21 AS '21', AOTESTS_22 AS '22', AOTESTS_23 AS '23', AOTESTS_24 AS '24', AOTESTS_25 AS '25', AOTESTS_26 AS '26', AOTESTS_27 AS '27', AOTESTS_28 AS '28', AOTESTS_29 AS '29', AOTESTS_30 AS '30', AOTESTS_31 AS '31', AOTESTS_32 AS '32', AOTESTS_33 AS '33', AOTESTS_34 AS '34', AOTESTS_35 AS '35', AOTESTS_36 AS '36', AOTESTS_37 AS '37', AOTESTS_38 AS '38', AOTESTS_39 AS '39', AOTESTS_40 AS '40', AOTESTS_41 AS '41', AOTESTS_42 AS '42', AOTESTS_43 AS '43', AOTESTS_44 AS '44', AOTESTS_45 AS '45', AOTESTS_46 AS '46', AOTESTS_47 AS '47', AOTESTS_48 AS '48', AOTESTS_49 AS '49', AOTESTS_50 AS '50', AOTESTS_51 AS '51', AOTESTS_52 AS '52', AOTESTS_53 AS '53', AOTESTS_54 AS '54', AOTESTS_55 AS '55', AOTESTS_56 AS '56', AOTESTS_57 AS '57', AOTESTS_58 AS '58', AOTESTS_59  AS '59')) O;

Version of the Orders table with ConcatenatedTests

WITH ConcatenatedTests as (
SELECT
"AA_ID","ORDER#","TEST0"  || ' ' ||  "TEST1"  || ' ' ||  "TEST2"  || ' ' ||  "TEST3"  || ' ' ||  "TEST4"  || ' ' ||  "TEST5"  || ' ' ||  "TEST6"  || ' ' ||  "TEST7"  || ' ' ||  "TEST8"  || ' ' ||  "TEST9"  || ' ' ||  "TEST10"  || ' ' ||  "TEST11"  || ' ' ||  "TEST12"  || ' ' ||  "TEST13"  || ' ' ||  "TEST14"  || ' ' ||  "TEST15"  || ' ' ||  "TEST16"  || ' ' ||  "TEST17"  || ' ' ||  "TEST18"  || ' ' ||  "TEST19"  || ' ' ||  "AOTESTS_20"  || ' ' ||  "AOTESTS_21"  || ' ' ||  "AOTESTS_22"  || ' ' ||  "AOTESTS_23"  || ' ' ||  "AOTESTS_24"  || ' ' ||  "AOTESTS_25"  || ' ' ||  "AOTESTS_26"  || ' ' ||  "AOTESTS_27"  || ' ' ||  "AOTESTS_28"  || ' ' ||  "AOTESTS_29"  || ' ' ||  "AOTESTS_30"  || ' ' ||  "AOTESTS_31"  || ' ' ||  "AOTESTS_32"  || ' ' ||  "AOTESTS_33"  || ' ' ||  "AOTESTS_34"  || ' ' ||  "AOTESTS_35"  || ' ' ||  "AOTESTS_36"  || ' ' ||  "AOTESTS_37"  || ' ' ||  "AOTESTS_38"  || ' ' ||  "AOTESTS_39"  || ' ' ||  "AOTESTS_40"  || ' ' ||  "AOTESTS_41"  || ' ' ||  "AOTESTS_42"  || ' ' ||  "AOTESTS_43"  || ' ' ||  "AOTESTS_44"  || ' ' ||  "AOTESTS_45"  || ' ' ||  "AOTESTS_46"  || ' ' ||  "AOTESTS_47"  || ' ' ||  "AOTESTS_48"  || ' ' ||  "AOTESTS_49"  || ' ' ||  "AOTESTS_50"  || ' ' ||  "AOTESTS_51"  || ' ' ||  "AOTESTS_52"  || ' ' ||  "AOTESTS_53"  || ' ' ||  "AOTESTS_54"  || ' ' ||  "AOTESTS_55"  || ' ' ||  "AOTESTS_56"  || ' ' ||  "AOTESTS_57"  || ' ' ||  "AOTESTS_58"  || ' ' ||  "AOTESTS_59" tests
FROM ORDERS
)

select * from ConcatenatedTests WHERE "ORDER#"='B4060003';

Full Examples

Calculating turn around time, from Receive Time to Verified Time for tests ordered by a specific Ward (RFFT)

  SELECT distinct TEST_ID,Name, SampleCount,Average_TAT_Minutes, ROUND(Average_TAT_Minutes/60,2) Average_TAT_Hours, ROUND(Average_TAT_Minutes/1440, 2) Average_TAT_Days FROM (
    SELECT TEST_ID, ROUND(AVG(MINUTESDIFFERENCE)) Average_TAT_Minutes, Count(MINUTESDIFFERENCE) SampleCount FROM (
       SELECT * FROM (
         select O."ORDER#",
         ROUND((NVL2(tr.date_verified,TO_DATE(TO_CHAR(tr.date_verified,'yyyy-mm-dd') || TO_CHAR(tr.time_verified,'HH24:MI'),'yyyy-mm-ddHH24:MI'),NULL) - NVL2(sp.date_receive,TO_DATE(TO_CHAR(sp.date_receive,'yyyy-mm-dd') || TO_CHAR(sp.time_receive,'HH24:MI'),'yyyy-mm-ddHH24:MI'),NULL))*24*60) MinutesDifference,
         TR.test_id from ORDERS O JOIN STAYS S ON S.AA_ID = O.PLAB_ACT 
         JOIN Test_Results TR on TR.ACT_ATEST = O.AA_ID
         INNER JOIN SPECIMENS SP ON (SP.ACT_ASPEC = O.AA_ID AND SP."TYPE" = TR.SPECIMEN_TYPE AND SP."STATION_ID" = TR."STATION_ID")
         WHERE S.CLINIC = 'RFFT' 
      ) TAT WHERE MINUTESDIFFERENCE is NOT NULL
    ) av group by TEST_ID
  ) TATr JOIN Tests on TATr.test_id = Tests.ID 
  WHERE Average_TAT_Minutes > 0 order by TEST_ID

Get counts From a test result range

Get the count of all results in the range of 0-30, 31-70, 71-100, 101-500, 501-1000

Each range has a corresponding column. That column is either a 1 or a 0 depending on the value of the test. Sum each of those columns to determine the count of tests in each range. Then use LISTAGG to list those results in a final column.

SELECT STATION_ID, TO_CHAR(RL.TEST_DATE, 'DD/MM'), SUM(C1) "0-30", SUM (C2) "31-70", SUM(C3) "71-100", SUM(C4) "101-500", SUM(C5) "501-1000", SUM(C6) "1001+", LISTAGG(RES, ', ') WITHIN 
GROUP (ORDER BY RES) AS RESULTLIST FROM
(SELECT TEST_RESULTS.TEST_DATE,
    TEST_RESULTS.STATION_ID,
    TEST_RESULTS."GROUP_TEST#",
    TEST_RESULTS.TEST_ID, CAST(REPLACE(REPLACE(TEST_RESULTS."RESULT", ' @E2F',''),'< ','')AS INT) AS RES,
   CASE
   WHEN REPLACE(REPLACE(TEST_RESULTS."RESULT", ' @E2F',''),'< ','') <30 THEN 1 ELSE 0 
   END C1,
   CASE
   WHEN REPLACE(REPLACE(TEST_RESULTS."RESULT", ' @E2F',''),'< ','')  BETWEEN 31 AND 70 THEN 1 ELSE 0
   END C2,
    CASE
   WHEN REPLACE(REPLACE(TEST_RESULTS."RESULT", ' @E2F',''),'< ','')  BETWEEN 71 AND 100 THEN 1 ELSE 0
   END C3,
    CASE
   WHEN REPLACE(REPLACE(TEST_RESULTS."RESULT", ' @E2F',''),'< ','') BETWEEN 101 AND 500 THEN 1 ELSE 0
   END C4, 
    CASE
   WHEN REPLACE(REPLACE(TEST_RESULTS."RESULT", ' @E2F',''),'< ','') BETWEEN 501 AND 1000 THEN 1 ELSE 0
   END C5, 
   CASE
   WHEN REPLACE(REPLACE(TEST_RESULTS."RESULT", ' @E2F',''),'< ','') > 1000 THEN 1 ELSE 0
   END C6 
  FROM ORDERS
  INNER JOIN TEST_RESULTS
  ON ORDERS.AA_ID                   = TEST_RESULTS.ACT_ATEST
  WHERE (TEST_RESULTS."GROUP_TEST#" = 'ESTD')
  AND (ORDERS."ORDER#" BETWEEN 'A6010000' AND 'A7309999')
  AND TEST_RESULTS."RESULT" <> '.')RL
  GROUP BY STATION_ID, TO_CHAR(RL.TEST_DATE, 'DD/MM')

Identify tests that were resulted as .ND, make sure they are called by pulling the !CALLED flags also pull the Result comments. And if it is a glucose that is .ND, include any the GLUNC results

SELECT * FROM (SELECT X."ORDER#", X.PATIENTNAME, X.ORDERMESSAGE,
LISTAGG(TEST_ID || ' = ' || "RESULT" || ' : ' || TECH_ID || NVL2(RESULTMESSAGE,' # ' || RESULTMESSAGE, ''), chr(13) || chr(10)) WITHIN GROUP (ORDER BY "ORDER#") ||
LISTAGG(NVL2(TRGLUNC_TESTID, chr(13) || chr(10) || TRGLUNC_TESTID || ' = ' || TRGLUNC_RESULT || ' : ' || TRGLUNC_TECHID,'')) WITHIN GROUP (ORDER BY "ORDER#") ||
REGEXP_REPLACE(LISTAGG(NVL2(TRSPRB1_TESTID, chr(13) || chr(10) || TRSPRB1_TESTID || ' = ' || TRSPRB1_RESULT || NVL2(TRSPRB1_TECHID,' : ' || TRSPRB1_TECHID,''),''),':::') WITHIN GROUP (ORDER BY "ORDER#"),':::.*','',1,0,'n') ||
REGEXP_REPLACE(LISTAGG(NVL2(TRSPRB2_TESTID, chr(13) || chr(10) || TRSPRB2_TESTID || ' = ' || TRSPRB2_RESULT || NVL2(TRSPRB2_TECHID,' : ' || TRSPRB2_TECHID,''),''),':::') WITHIN GROUP (ORDER BY "ORDER#"),':::.*','',1,0,'n') ||
REGEXP_REPLACE(LISTAGG(NVL2(TRSCALL_TESTID, chr(13) || chr(10) || TRSCALL_TESTID || ' = ' || TRSCALL_RESULT || NVL2(TRSCALL_TECHID,' : ' || TRSCALL_TECHID,''),''),':::') WITHIN GROUP (ORDER BY "ORDER#"),':::.*','',1,0,'n') TEST_RESULT
FROM (SELECT O."ORDER#",
TR.TEST_ID,
TR."RESULT",
TR.TECH_ID,
TRGLUNC.TEST_ID TRGLUNC_TESTID,
TRGLUNC."RESULT" TRGLUNC_RESULT,
TRGLUNC.TECH_ID TRGLUNC_TECHID,

TRSPRB1.TEST_ID TRSPRB1_TESTID,
TRSPRB1."RESULT" TRSPRB1_RESULT,
TRSPRB1.TECH_ID TRSPRB1_TECHID,

TRSPRB2.TEST_ID TRSPRB2_TESTID,
TRSPRB2."RESULT" TRSPRB2_RESULT,
TRSPRB2.TECH_ID TRSPRB2_TECHID,

TRSCALL.TEST_ID TRSCALL_TESTID,
TRSCALL."RESULT" TRSCALL_RESULT,
TRSCALL.TECH_ID TRSCALL_TECHID,
P.LAST_NAME || ', ' || P.FIRST_NAME as PatientName,
REPLACE(TR_M.TEXT, CHR(0),'') ResultMessage, 
    REPLACE(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(LISTAGG(Spec_M.TEXT, '') WITHIN GROUP (ORDER BY Spec.AA_ID, Spec_M.ASPEC_MES_SORT ASC),'!*[^!]*(!CALLED [^!]*)','\1'),'.!CALLED-',''),CHR(0),''),';!CALLED',';' || CHR(13) || CHR(10) || '!CALLED') OrderMessage
    FROM TEST_RESULTS TR 
    JOIN ORDERS O 
        ON TR.ACT_ATEST = O.AA_ID 
    LEFT JOIN TEST_RESULTS TRGLUNC 
        ON (O.AA_ID = TRGLUNC.ACT_ATEST AND TR."RESULT" ='.ND' AND TR.TEST_ID = 'GLU' AND 'GLUNC' = TRGLUNC.TEST_ID)
    LEFT JOIN TEST_RESULTS TRSPRB1 
        ON (O.AA_ID = TRSPRB1.ACT_ATEST AND TR."RESULT" ='.ND' AND 'SPRB1' = TRSPRB1.TEST_ID)
    LEFT JOIN TEST_RESULTS TRSPRB2 
        ON (O.AA_ID = TRSPRB2.ACT_ATEST AND TR."RESULT" ='.ND' AND 'SPRB2' = TRSPRB2.TEST_ID)
    LEFT JOIN TEST_RESULTS TRSCALL 
        ON (O.AA_ID = TRSCALL.ACT_ATEST AND TR."RESULT" ='.ND' AND 'SCALL' = TRSCALL.TEST_ID)
    JOIN STAYS S ON O.PLAB_ACT = S.AA_ID JOIN Specimens Spec ON O.AA_ID = Spec.ACT_ASPEC JOIN PATIENTS P ON P.AA_ID = S.PAT_PLAB LEFT JOIN MESSAGES TR_M ON TR.AA_ID = TR_M.ATEST_MES LEFT JOIN MESSAGES Spec_M ON Spec.AA_ID = Spec_M.ASPEC_MES
    WHERE O."ORDER#" BETWEEN 'A8200715' AND 'A8200718' AND TR.STATION_ID In ('SCA1','SMAN','SABG','SOC','SPOR') AND (TR."RESULT" ='.ND')
    group by O."ORDER#", P.LAST_NAME,P.FIRST_NAME, TR.TECH_ID, TR.TEST_ID, TR."RESULT", TR_M.TEXT,
    TRGLUNC.TECH_ID, TRGLUNC."TEST_ID", TRGLUNC."RESULT",
    TRSPRB1.TECH_ID, TRSPRB1."TEST_ID", TRSPRB1."RESULT",
    TRSPRB2.TECH_ID, TRSPRB2."TEST_ID", TRSPRB2."RESULT",
    TRSCALL.TECH_ID, TRSCALL."TEST_ID", TRSCALL."RESULT"
  ) X
  group by "ORDER#", PATIENTNAME, ORDERMESSAGE)
WHERE rownum < 1000

Get a list of results for a specific ward and a specific set of tests. Group concatenate the tests.

SELECT ORDER#, PATIENT_NAME, DATE_OF_BIRTH, 
LISTAGG(TEST_ID || ' = ' || RESULT, ', ') WITHIN GROUP (ORDER BY TEST_ID DESC) TESTRESULTS,

DATETIME_RECEIVED, DATETIME_VERIFIED FROM (
         select O."ORDER#",
         P.LAST_NAME || ', ' || p.FIRST_NAME || ' ' || p.middle_initial PATIENT_NAME,
         P.DATE_OF_BIRTH,
         TR.test_id,
         TR.RESULT,
         NVL2(SP.date_receive,TO_CHAR(TO_DATE(TO_CHAR(SP.date_receive,'yyyy-mm-dd') || TO_CHAR(SP.time_receive,'HH24:MI'),'yyyy-mm-ddHH24:MI'),'yyyy-mm-dd HH24:MI'),NULL) DateTime_Received,
         NVL2(tr.date_verified,TO_CHAR(TO_DATE(TO_CHAR(tr.date_verified,'yyyy-mm-dd') || TO_CHAR(tr.time_verified,'HH24:MI'),'yyyy-mm-ddHH24:MI'),'yyyy-mm-dd HH24:MI'),NULL) DateTime_Verified
         from ORDERS O 
         JOIN STAYS S ON S.AA_ID = O.PLAB_ACT 
         JOIN Test_Results TR on TR.ACT_ATEST = O.AA_ID
         JOIN PATIENTS P On P.AA_ID = S.PAT_PLAB
         INNER JOIN SPECIMENS SP ON (SP.ACT_ASPEC = O.AA_ID AND SP."TYPE" = TR.SPECIMEN_TYPE AND SP."STATION_ID" = TR."STATION_ID")
         WHERE S.CLINIC = 'A9A4C' AND O."ORDER#" between 'B3010000' AND 'B4060000' AND TR.TEST_ID in ('PTI','INR','INRC', 'INRD')
         )
         group by "ORDER#",PATIENT_NAME, DATE_OF_BIRTH, DATETIME_RECEIVED, DATETIME_VERIFIED
   order by "ORDER#"

Take the OrderMessage tags and merge them into a single string. Then drop tags that are not "!CALLED" tags

SELECT 
"ORDER#",
REPLACE(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE('XX>><<XX' || RawOrderMessage || 'XX>><<XX','(!CALLED\d* [^!]*)','XX>>\1<<XX',1,0),'<<XX.*?XX>>','<<XXXX>>',1,0),'^.*?XX>>','XX>>',1,0),'(.*)(<<XX.*)$','\1<<XX',1,0),'XX>>',''),'<<XX','') Called,
RawOrderMessage
FROM (
    SELECT X."ORDER#",X.RawOrderMessage
    FROM (
        SELECT O."ORDER#",
        REGEXP_REPLACE(REPLACE(LISTAGG(Spec_M.TEXT, '') WITHIN GROUP (ORDER BY Spec.AA_ID, Spec_M.ASPEC_MES_SORT ASC),CHR(0),''),'![A-Z0-9]*-','') RawOrderMessage
        FROM TEST_RESULTS TR 
        JOIN ORDERS O ON TR.ACT_ATEST = O.AA_ID 
        JOIN STAYS S ON O.PLAB_ACT = S.AA_ID JOIN Specimens Spec ON O.AA_ID = Spec.ACT_ASPEC
        LEFT JOIN MESSAGES Spec_M ON Spec.AA_ID = Spec_M.ASPEC_MES AND SPEC_M.TYPE = 'T'
        WHERE O."ORDER#" BETWEEN 'B2010000' AND 'B2020000'
          AND TR.STATION_ID In ('SCA1','SMAN','SABG','SOC','SPOR') AND (TR."RESULT" ='.ND')
        group by O."ORDER#", TR.TEST_ID
      ) X
    group by "ORDER#", RawOrderMessage
  ) MM
WHERE rownum < 1000
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment