Written by

Enterprise Application Development Consultant at The Ohio State University Wexner Medical Center

MOD

Question Scott Roth · Jun 9, 2020

Querying a Cache Table from a Business Rule

#Ensemble #Business Rules #ObjectScript #SQL

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

Add reply

Comments

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

Scott Roth Jun 9, 2020 to Eduard Lebedyuk

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

0 0

Eduard Lebedyuk Jun 9, 2020 to Scott Roth

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

Scott Roth Jun 9, 2020 to Eduard Lebedyuk

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

0 0

Eduard Lebedyuk Jun 9, 2020 to Scott Roth

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

As you presumably want to return the value.

0 0

Scott Roth · Jun 10, 2020

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

0 0

Eduard Lebedyuk Jun 10, 2020 to Scott Roth

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

0 0

Scott Roth Jun 10, 2020 to Eduard Lebedyuk

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.

0 0

Marc Mundt Jun 10, 2020 to Scott Roth

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

0 0