· Sep 14, 2023

How to do a SQL query in DTL and map PV1 7.1 to results of query

I need to run a SQL query and use the output to map PV1 7.1. The query is :

FROM TestTable


When I run this query with the 'TEST PROVIDER'  I do pull the ID in question but I can't figure out how to do it from the DTL given that there are various providers sent in PV 1 7 . Any assistance will be greatly appreciated. 

Product version: IRIS 2021.2
Discussion (8)2
Log in or sign up to continue

You can add sql from the add action and place your embedded sql query and get the values in host variable. Then set the sql return value to the field directly like below.

XData DTL [ XMLNamespace = "" ]
<transform sourceClass='EnsLib.HL7.Message' targetClass='EnsLib.HL7.Message' sourceDocType='2.3.1:ADT_A01' targetDocType='2.3.1:ADT_A01' create='new' language='objectscript' >
<assign value='source.{PV1:SetIDPV1}' property='target.{PV1:SetIDPV1}' action='set' disabled='1' />
<![CDATA[ select count(id) INTO :value from Sample.Person]]></sql>
<assign value='value' property='target.{PV1:AdmissionType}' action='set' />

Hey Christine.

If I'm reading your question and subsequent replies correctly, you're trying to take the value of PV1:7.1, and then use that in a SQL query. The answer has been given by Ashok when you put their replies together, but hopefully putting it all into a single response will make things easier to follow.

If this is the case, then you will want to do the following:

Step 1: Set a variable to the value of PV1:7.1:

Step 2: Add a code block, and use this to run your sql query:

Step 3: Do what you need to with the value of ID - for the sake of this response, I'm just setting the value of PV1:7.2 to the ID returned from the query that inserted into the variable "Output":

It's worth knowing that, when working with Embedded SQL, prefixing a variable with a colon is how you can pass variables in and out of the Embedded SQL section of code. However it's a bit clearer when working directly with ObjectScript vs a DTL.

For example, if we had the following table:

1 ABC 123
2 DEF 234

We could have the following in ObjectScript:

    Set X = "" // X is null
    Set Y = "ABC"
        SELECT COL_B
        into :X
        From TestTable
        WHERE COL_A = :Y
    WRITE X //X is 123