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

Question

Question

Christine Nyamu · Sep 14, 2023

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)2

Log in or sign up to continue

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:

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

0 0

score 0 · Answer 1 · 2023-09-15T00:46:07-04:00

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

Try Below:

score 0 · Answer 2 · 2023-09-20T11:35:55-04:00

@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'

score 1 · Answer 3 · 2023-09-15T01:29:47-04:00

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>
}

score 0 · Answer 4 · 2023-09-20T11:37:19-04:00

@Ashok Kumar 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

score 0 · Answer 5 · 2023-09-20T13:07:42-04:00

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

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

score 0 · Answer 6 · 2023-09-25T09:40:36-04:00

Christine Nyamu · Sep 25, 2023

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

0 0

score 0 · Answer 7 · 2023-09-25T09:53:29-04:00

Thank you @Julian Matthews for the clear and concise response, it worked! Thank you @Arshiya Syeda and @Ashok Kumar as well for giving me something to start working with.