Scott Roth · Jan 7, 2021

Looking up value in Cache Table via a Business Rule (Routing)

I need another set of eyes to look at the following. Instead of a Data Lookup table, I wanted to try to lookup a value against a Cache table that I had build. So I thought I could write a function to run a simple SQL query against the Cache Table and return me a %Boolean value back to my routing rule. However today I found that it was not working properly.

Can someone take a look at the following method and verify that I am doing this correct?

index = column name

value  = HL7 field that I am passing into the method

ClassMethod TecsysUnitExists(index As %String, value As %String) As %Boolean


        set ValidUnitID = ""

        &sql(SELECT :index INTO :ValidUnitID 

            FROM osuwmc_Tecsys.UnitReference WHERE :index=:value)


        if ValidUnitID '= ""


            quit 1


        quit 0





Cache for UNIX (IBM AIX for System Power System-64) 2018.1.3 (Build 414U) Mon Oct 28 2019 11:24:02 EDT
Product version: 
Ensemble 2018.1
2 0 3 50


If "index" is a column name in the table then it shouldn't have a colon at the front.

Also, it's best to check the SQLCODE in order to handle errors:

if SQLCODE < 0 {
   // Do something about an error
} else {
   quit myReturnValue

I didn't read your post carefully enough -- I see that you're passing the column name as a variable named "index". Embedded SQL doesn't allow using host variables (e.g. :myVar ) in place of an identifier:

A host variable cannot be used to specify an SQL identifier, such as a schema name, table name, field name, or cursor name. A host variable cannot be used to specify an SQL keyword.

The alternative would be to switch from embedded SQL to Dynamic SQL, which would allow you to manually construct your query string before executing it:

set myQuery="SELECT "_index_" FROM osuwmc_Tecsys.UnitReference WHERE "_index_" = ?"

"value" would then be passed as a parameter when executing the statement and you would use "%Get" in the result set to fetch the value for ValidUnitID.

So something like this...