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

 

Thanks

Scott

Product version: Ensemble 2018.1
$ZV: Cache for UNIX (IBM AIX for System Power System-64) 2018.1.3 (Build 414U) Mon Oct 28 2019 11:24:02 EDT
Discussion (4)2
Log in or sign up to continue

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.