Question
· 17 hr ago

Using a Linked Table (SQL) as a Filter in a Business Rule

I had a need for a Filter, but did not want to recreate the wheel by creating another Data Lookup Table, so instead I created a Linked Table that points to a MS SQL Table outside of IRIS.

Once I had the Linked Table, I created a Class Method Function that would query the Linked Table and return a 1 if a result came back.

ClassMethod CheckPDMProviderType(pInput As %String) As %Boolean
{
    set ExtDisplay = ""
 		&sql(SELECT SecurityGroup_k INTO :ExtDisplay
        FROM osuwmc_CPD_SQL.Ref_SecurityGroup WHERE PDMExtDisplay = 1 AND SecurityGroup_k = :pInput)
    if ExtDisplay = "" {
        quit 0
    }
    quit 1
}

However, it doesn't seem like the Results of the Class Method are coming back correctly 100% of the time.

Is there an easier way to use the Linked Table to filter within a Business Rule? Are we able to project Linked Tables to Data Lookup Tables?

Product version: IRIS 2025.1
$ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2025.1 (Build 230_2U) Wed Jun 4 2025 18:50:44 EDT [HealthConnect:8.2.2]
Discussion (1)2
Log in or sign up to continue

it doesn't seem like the Results of the Class Method are coming back correctly 100% of the time.

This is VERY strange, I would add a test/check if an error occur testing SQLCODE and %msg after &SQL() to see if/when/why it fails.

When using embedded SQL you are not supposed to check/access a returned variable (ExtDisplay in your case) before checking for success/failure via SQLCODE variable.

If you need to check if the query found a row, you should check the SQLCODE value, not ExtDisplay  value.