0
448
Question Christine Nyamu · Sep 14, 2023

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

#InterSystems IRIS for Health #SQL

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

SELECT ID
FROM TestTable
WHERE ProviderName = 'TEST,PROVIDER' AND IDType= 'BPI'

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)0
Log in or sign up to continue

Comments

Arshiya Syeda · Sep 15, 2023

You can always use <code></code> in DTL

Try Below:

<code>
<![CDATA[
    &sql(SELECT ID
    FROM TestTable
    WHERE ProviderName = 'TEST,PROVIDER' AND IDType= 'BPI')
]]>
</code>

0
Christine Nyamu  Sep 20, 2023 to Arshiya Syeda

@Arshiya Syeda 
Thanks for the reply. My greatest challenge is how to pull that PV1 7 value given that there are different providers e.g, 'TEST,PROVIDER', 'TEST,PROVIDER1', 'TEST,PROVIDER2', 'TEST,PROVIDER3' etc. so I can not hard code it to only use 'TEST,PROVIDER'

0
Ashok Kumar T · Sep 15, 2023

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 = "http://www.intersystems.com/dtl" ]
{
<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' />
<sql>
<![CDATA[ select count(id) INTO :value from Sample.Person]]></sql>
<assign value='value' property='target.{PV1:AdmissionType}' action='set' />
</transform>
}
0
Christine Nyamu  Sep 20, 2023 to Ashok Kumar T

@Ashok Kumar T Thanks for the reply. My greatest challenge is how to pull that PV1 7 value given that there are different providers e.g, 'TEST,PROVIDER', 'TEST,PROVIDER1', 'TEST,PROVIDER2', 'TEST,PROVIDER3' etc. I might be missing something in the code that you sent or might not be clearly understaning it. Thanks 

0
Ashok Kumar T  Sep 20, 2023 to Christine Nyamu

Have you tried to pass the provider as a host variable in the query

SELECT ID FROM TestTable WHERE ProviderName = :PROVIDER AND IDType= 'BPI'
0
Julian Matthews · Sep 22, 2023

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:

ID COL_A COL_B
1 ABC 123
2 DEF 234

We could have the following in ObjectScript:

SetX = ""// X is nullSet Y = "ABC"
    &SQL(SELECT COL_B
        into :X
        From TestTable
        WHERE COL_A = :Y
    )
    WRITEX//X is 123
0
Christine Nyamu  Sep 25, 2023 to Julian Matthews

Thank you @Julian.Matthews7786. I will try your suggestion. 

0