Using Lookup Tables

[Steve Roselius]

In populating a Lookup table, there are a couple of options:
1. Manually entering the data using the EMP interface (Maintenance/
Lookup Settings); or
2. Calling the %Import method, if you are fortunate enough to have
your data wrapped by XML

I am considering a third approach -- using SQL:

INSERT INTO Ens_Util.LookupTable ...

Has anybody done this before? Or, given the complex key structure, is
it too risky to contemplate?

Thanks in advance.

[David Loveluck]

Steve

the attached sample maps a class (or table) DRL.STARDIETARY to the
lookup table in an appropriate way for use as SQL (or object) access to
the STARDIETARY lookup table.

As always, don't rely on my testing - it is only an example.

Dave

PS: For anyone unfamiliar with Ensemble storage, there is a default
mapping for classes or tables to the underlying data structures, which
allow you to forget all about it. They simply work out of the box. But
there are reasons why you might want to control that mapping. (The most
common is for support of legacy code.)

In this case, it was decided to by-pass SQL, store all look up tables in
the same structure (^Ens.LookupTable) and make it as fast as possible
for the source to target translation. That structure didn't match the
default class storage, but the storage section added here takes care of
it. If you define a storage definition similar to this, you can use SQL
and once again forget about the underlying structure.





David Loveluck
617 225 3122

[David Loveluck]

I meant to say that applied to older versions. Of course, from 2008.2
there is a built in projection

SELECT KeyName,DataValue FROM Ens_Util.LookupTable WHERE TableName =
'myTab'

See:

http://192.168.19.1:57775/csp/docbook/DocBook.UI.Page.cls?KEY=EGDV_prod_
crea
te#EGDV_function_lookup

So it's possible to use the GUI and still have a way to query the
information without any additional code.

[Steve Roselius]

I've not asked my question clearly enough. Let me try again.

I would like to add new records to an existing lookup table. I happen
to know that it will contain the following new values:

Foo, Bar
Howdy, Doody
Mickey, Mouse

and so on. When an incoming record contains the value "Foo", I intend
to change it to "Bar" via DTL.

I do not want to use the EMP interface to load these new records.

Is there anything (such as common sense) that should stop me from
doing the following?

INSERT INTO Ens_Util.LookupTable (ID,DataValue,KeyName,TableName)
VALUES ("MyTable||Foo",Bar,Foo,MyTable)

Thanks

On May 28, 5:14 pm, David Loveluck <David.Lovel...@intersystems.com>
wrote:

> Thanks in advance.- Hide quoted text -
>
> - Show quoted text -

[André Cerri]

Steve, what version? In more recent kits the SQL approach works fine. I did
this in my Cloverleaf converter for their lookup tables.