New post
AllAccepted answers
Jeffrey Drumm · Feb 7, 2020

As a general rule, I'd suggest using the StartsWith() function in the DTL wizard when comparing date fields. In your case, the Birthdate field very likely does not include a Time component at a resolution of seconds. If it did, though, you could run into many combinations where the date value from CurrentDateTIme() would match.

For example, Contains() would return true on the 19th or 20th of February 2020 against "20200220200219" ... February 20th 2020 at 8:02:19pm. This would certainly be a rare occurrence, but not impossible.

Jeffrey Drumm · Jan 16, 2020

Thanks for the response, fellow Jeffrey!

The tables I'm currently working with are tiny, literally no more than a couple dozen message header and body records. The query in the IRIS SQL shell (and management portal SQL facility) returns results immediately.

I think we have some sort of obscure bug here. This is with IRIS for Health CE 2019.1 running on Ubuntu (not a docker image), and the client is Windows 10 using IRIS ADO.NET support. Same result when using the ODBC drivers, though.

I think you're right on reaching out to WRC ... thanks again.

Jeffrey Drumm · Jan 14, 2020

I should also point out (after doing some additional experimentation) that this method works for individual fields as well:

Again, I should mention that what you see in the DTL Test facility does not represent what will happen in the transformation. I believe this is an artifact of the way the Windows clipboard handles EOL characters in a cut/paste operation.

Jeffrey Drumm · Jan 14, 2020

I'm pretty sure you don't want a newline character anywhere in the target OBX segments, so this should do the trick:

Unfortunately, this does not appear to do the right thing in the DTL Test tool. You can verify it works at the Caché/IRIS prompt, though:

You can, of course, also verify that it works by calling it from a routing rule laugh

Jeffrey Drumm · Jan 11, 2020

The fact that the DB1 and OBX segments are shown in the color black is an indicator that your HL7 schema does not match the message's actual structure. Unless you correct that in the schema (which may require creating a custom schema) you won't be able to reference the OBX (or DB1) segments in your routing rule using the standard conventions.

Jeffrey Drumm · Jan 9, 2020

I've added a query to the HICG.HL7 class that lets you leverage Ensemble's HL7 Search Tables to select messages by the fields specified for indexing. The new version is available on the HL7 Spy website.

An example, using the default search table to select messages by PatientID/MRN:

-- region 8 - message rows by SearchTable

SELECT
    tbl.BodyId,
    msg.TimeCreated,
    msg.Name,
    msg.DocType,
    HICG.GetMsg(msg.Id) As Message
    FROM
        HICG.TblSrch('EnsLib.HL7.SearchTable','PatientID','4444') tbl
    INNER JOIN
        EnsLib_HL7.Message msg
    ON
        tbl.BodyId = msg.Id
-- endregion
Jeffrey Drumm · Jan 6, 2020

So to clarify ... the class method isn't so much a stored procedure as a custom function. It should return an HL7 message as a  string, which will be represented as a column in the result set generated by a SELECT. So technically, the SQLCODE I'm getting back is correct for what I'm doing, which is calling a function against a column value in the query. If the function fails, the query fails, and the reason for the query failure is the function failure. That's how it's coming back to ADO/ODBC when I force a failure, and the reason I asked the question in the first place is that sometimes I'm just not too smart :D

Jeffrey Drumm · Jan 4, 2020

Hi Eduard, and thanks!

 I had already tried that option, but discarded it because the SQL code returned is <-149>:<SQL Function encountered an error>, with my error code and text as a subordinate (child?) error. Is there any way to set the "parent" SQLCODE and associated error text? Or is that effectively the "query" error, with the child error generated by the SqlProc?

Jeffrey Drumm · Dec 30, 2019

Execution details for routing rules are located in Ens.Rule.Log. The available values are ID, ActivityName, ConfigName, CurrentHeaderId, DebugId, EffectiveBegin, EffectiveEnd, ErrorMsg, IsError, Reason, ReturnValue, RuleName, RuleSet, SessionId, and TimeExecuted.

In the SQL facility, you can query Ens_Rule.log:

SELECT ID, ConfigName, CurrentHeaderId, RuleName, RuleSet, SessionId, TimeExecuted FROM Ens_Rule.Log