Show Mirth Connect errors by destination with PID.3 and PV1.19 from source message with short error and detail description

mirth-channel-error.sql

select 
dm.message_id, status,connector_name ,
to_char(received_date AT TIME ZONE 'US/Eastern', 'YYYY-MM-DD hh24:mi') || ' US/Eastern' as message_date, 
(regexp_matches(mc.content, 'PID\|(?:.*?\|){2}(.*?)\|'))[1] as pid_3_mrns,
(regexp_matches(mc.content, 'PV1\|(?:.*?\|){18}(.*?)\|'))[1] as pv1_19_visit_id,
(regexp_matches(mc_error.content, '.*ERROR MESSAGE:\s+(.*?)', 'n'))[1] as error_message,
(regexp_matches(mc_error.content, '.*DETAILS:\s+(.*?)', 'n'))[1] as detail_message
from mirth.public.d_mm76 dm 
inner join d_mc76 mc on mc.message_id = dm.message_id and mc.metadata_id = 0 and mc.content_type = 1
inner join d_mc76 mc_error on mc_error.message_id = dm.message_id and mc_error.metadata_id = dm.id  and mc_error.content_type = 12
where status = 'E'
order by 4 asc