Querying a Cache Table from a Business Rule

Primary tabs

After all these years of doing basic Ensemble work, I am just beginning to venture into using Cache Tables instead of either Data Lookup tables, or what I know of Outside SQL tables using JDBC.  I have several Cache SQL tables that I am building for a project I am working on.  

  • I was wondering if it was possible to use a SQL call to look up a value against a Cache SQL table  within a Business Rule? 
  • Or is this something that I would have to build a custom function for? 

Within the Business Rule I need to test to see if a Location value exists within this table, and if it does then process sending it through the DTL onto the Business Operation. 

I could use a Data Lookup table to do this, but do not want to duplicate the process if I don't have too.

Thanks

Scott

Replies

You'll need a Custom Function.

If the lookup field is indexed (Location in your case) you can use this function for all Lookup checks:

ClassMethod Exists(class As %Dictionary.CacheClassname, index As %String, value As %String) As %Boolean [ CodeMode = expression]
{
$classmethod(class, index _ "Exists", value)
}

So %Dictionary.CacheClassname would be my table name then? I don't have to use any :sql.... type code for it to do the lookup? What about the existing EXISTS function that already exists that is used for Data Lookup Tables (lut)?

So %Dictionary.CacheClassname would be my table name then?

Well, class name. If you want you can convert table name to class name with:

set:table'["." table=$$$DefaultSchema_"."_table	// support unqualified names
set class = $$$GetClassNameFromIQN(table)

I don't have to use any :sql.... type code for it to do the lookup? 

You can but you don't have to.

What about the existing EXISTS function that already exists that is used for Data Lookup Tables (lut)?

It's a separate function that works only with Data Lookup Table  (which are actually not tables at all).

So it would be like this...

ClassMethod CacheExists(table as %String , index As %String, value As %String) As %Boolean 
{
set:table'["." table=$$$DefaultSchema_"."_table // support unqualified names
set class = $$$GetClassNameFromIQN(table)

DO $classmethod(class, index _ "Exists", value)
}

...
quit $classmethod(class, index _ "Exists", value)

As you presumably want to return the value.

It appears my first attempt didn't work as expected..

ClassMethod CacheExists(table As %String, index As %String, value As %String) As %Boolean
{
set:table'["." table=$$$DefaultSchema_"."_table // support unqualified names
set class = $$$GetClassNameFromIQN(table)

quit $classmethod(class, index _ "Exists", value)
}

ERROR <Ens>ErrBPTerminated: Terminating BP SIU949502CaseRoutingRule # due to error: ERROR <Ens>ErrException: <PROPERTY DOES NOT EXIST>zevaluateRuleDefinition+193 ^osuwmc.CaseSchedulingRoutingRule.1 *osuwmc,EnsLib.HL7.MsgRouter.RoutingEngine -- logged as '-'
number - @'
if (##class(osuwmc.Functions).CacheExists(((pContext.osuwmc)_(pContext.Tecsys)_(pContext.Unit.DataTable)),(pContext.UnitID),(pContext.HL7.GetValueAt("RGSgrp("_(1)_").AIL:LocationResourceID("_(1)_").Facility.NamespaceID")))) {'
> ERROR <Ens>ErrException: <PROPERTY DOES NOT EXIST>zevaluateRuleDefinition+193 ^osuwmc.CaseSchedulingRoutingRule.1 *osuwmc,EnsLib.HL7.MsgRouter.RoutingEngine -- logged as '-'
number - @'
if (##class(osuwmc.Functions).CacheExists(((pContext.osuwmc)_(pContext.Tecsys)_(pContext.Unit.DataTable)),(pContext.UnitID),(pContext.HL7.GetValueAt("RGSgrp("_(1)_").AIL:LocationResourceID("_(1)_").Facility.NamespaceID")))) {'

I am not quite sure I am following... osuwmc.Functions is the main class file, we store our custom functions. We have used it in many other Business rules before for other functions we have created.  So doesn't that mean osuwmc property should exists? We store all of our files under the osuwmc schema.

I think you just need to change your business rule so the first parameter you pass to CacheExists is wrapped in quotes:

CacheExists("osuwmc_Tecsys...",...)