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?
Comments
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.