Accessing a Lookup Table in a custom Ens.Rule.FunctionSet function

[LKO]

Is there a way in a custom FunctionSet function to access the rows (both columns) of a Lookup Table?

Is so, can you explain how to do so?

[Dale du Preez]

Hi LKO,

What do you mean by "access" here? Perhaps it may be easier for me to understand your question if I know what you are trying to achieve.

Dale

On 10/18/2012 10:05 AM, LKO wrote:

Is there a way in a custom FunctionSet function to access the rows (both columns) of a Lookup Table?

Is so, can you explain how to do so?

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

[LKO]

I want to be able to do a Lookup in reverse. In other words, I want to use the Value as the key and the Key as the value. And I realize that this is appropriate only for a one-to-one relationship.

[LKO]

An InterSystems engineer has provided an SQL statement that should allow me to do what I need to do.

On Thursday, October 18, 2012 9:05:30 AM UTC-5, LKO wrote:

[LKO]

My function creates an SQL select statement for which the table is a Lookup Table that is supplied as a function argument.
This is working fine.
I'd like to, however, be able to have the function return an error code if the Lookup Table cannot be found or has been misspelled.
I use the SQL %Prepare method with my SQL statement. Reading the documentation for this method, it states that %Prepare returns one (indicating success) if "the query string is valid" and the "referenced tables exist in the current namespace". When I intentionally use a name for which there is not an existing Lookup Table, %Prepare still returns true.

HealthShare version 2012.2.

On Thursday, October 18, 2012 9:05:30 AM UTC-5, LKO wrote:

[Andre Cerri]

You could do it thru SQL, something like (stylized) select top 1 <column> from ens_util.lookuptable where tablename=? and see if there is a row returned.

 

But way more efficient would be to hit the global directly with something like:

 

If ‘$data(^Ens.LookupTable(“tablename”)) { return error code }

 

Maybe check for null values as well.

 

HTH

 

André

--

[LKO]

But how would I distinguish between a failure because the table doesn't exist and simply not finding a row that matches my search criterion?

On Thursday, October 25, 2012 11:29:52 PM UTC-5, Boca Rat wrote:

You could do it thru SQL, something like (stylized) select top 1 <column> from ens_util.lookuptable where tablename=? and see if there is a row returned.

 

But way more efficient would be to hit the global directly with something like:

 

If ‘$data(^Ens.LookupTable(“tablename”)) { return error code }

 

Maybe check for null values as well.

 

HTH

 

André

 

 

From: ensemble-in...@googlegroups.com [mailto:ensemble-in-healt...@googlegroups.com] On Behalf Of LKO
Sent: Friday, October 19, 2012 6:53 PM
To: Ensemble-in...@googlegroups.com
Subject: [InterSystems-EnsHlth] Re: Accessing a Lookup Table in a custom Ens.Rule.FunctionSet function

 

My function creates an SQL select statement for which the table is a Lookup Table that is supplied as a function argument.
This is working fine.
I'd like to, however, be able to have the function return an error code if the Lookup Table cannot be found or has been misspelled.
I use the SQL %Prepare method with my SQL statement. Reading the documentation for this method, it states that %Prepare returns one (indicating success) if "the query string is valid" and the "referenced tables exist in the current namespace". When I intentionally use a name for which there is not an existing Lookup Table, %Prepare still returns true.

HealthShare version 2012.2.

On Thursday, October 18, 2012 9:05:30 AM UTC-5, LKO wrote:

Is there a way in a custom FunctionSet function to access the rows (both columns) of a Lookup Table?

Is so, can you explain how to do so?

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.

To post to this group, send email to Ensemble-i...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

[Andre Cerri]

LKO, Do you mean in the calling code? Is this BPL or DTL?

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

--

You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.

To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com