Struggling with a SQL SELECT that is returning multiple searchable rows
I am struggling with returning a SQL Query Result that may have multiple rows that can be searched.
set query = "SELECT Facility FROM FROM osuwmc_EnterpriseDirDB.RelationshipMedCtrID WHERE OSUmedcenterID = ?"SET rset = ##class(%SQL.Statement).%New()
SET qStatus = rset.%Prepare(query)
SET rset = rset.%Execute($Get(ID))
do rset.%Display()I need to take the values that are returned and say search them for a single value. How would I go about returning the EnsLib.SQL.Snapshot into a Array or List for it to be searchable.
Can this be done internally within the DTL, so I don't have to go build a Business Process and Outbound SQL Adapter Operation?
Thanks
Comments
Just out of curiosity, can you include what you're searching for in the where clause of the initial query?
I have an Identifier that I receive in an HL7 MFN message, I need to take this Identifier and find the Locations that the Identifier is associated with, so I can set the Sending Facility value within the HL7 message to help filter out specific locations from being sent to the Health Share Provider Directory.
for example... ID1 might be associated with Site 1, Site 3. IF site 1 is valued and equals xxx, then send on else do not send on.
Here's how you can iterate through the results:
set query = "SELECT Facility FROM FROM osuwmc_EnterpriseDirDB.RelationshipMedCtrID WHERE OSUmedcenterID = ?"SET rset = ##class(%SQL.Statement).%New()
SET qStatus = rset.%Prepare(query)
SET rset = rset.%Execute($Get(ID))
while (rset.%Next()) {
// Check Facility value in each rowset facility=rset.%Get("Facility")
if (facility = "SOMEVALUE") {
//...
}
}For using this from a DTL you can put the query logic in a custom function which takes the identifier from the MFN and returns the facility.