Querying a Cache Table from a Business Rule

Question

Question

Scott Roth · Jun 9, 2020

#Business Rules #ObjectScript #SQL #Ensemble

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

Discussion (9)2

Log in or sign up to continue

Eduard Lebedyuk · Jun 9, 2020

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

0 0

Eduard Lebedyuk · Jun 9, 2020

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

0 0

Eduard Lebedyuk · Jun 9, 2020

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

As you presumably want to return the value.

0 0

score 0 · Answer 1 · 2020-06-09T09:25:36-04:00

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

score 0 · Answer 2 · 2020-06-09T13:32:14-04:00

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

score 0 · Answer 3 · 2020-06-10T16:15:58-04:00

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")))) {'

score 0 · Answer 4 · 2020-06-10T16:24:50-04:00

Eduard Lebedyuk · Jun 10, 2020

I don't think osuwmc property exist in your Rule context.

0 0

score 0 · Answer 5 · 2020-06-10T16:37:20-04:00

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.

score 1 · Answer 6 · 2020-06-10T16:51:32-04:00

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...",...)