SELECTING distinct keys from a collection

Question

Question

Derek Day · Dec 15, 2015

Is there a way to select distinct keys from an field that has a collection index? I have a field defined as follows: Property data As %Library.String(COLLATION = "EXACT", MAXLEN = "", TRUNCATE = 0); Index data On data(KEYS) [ Type = bitmap ]; And I define a build value array method that parses my data outputs an array in the format array(KEYS)=VALUES. This is very useful because I can query my data using criteria such as WHERE FOR SOME %ELEMENT(data) (%KEY='param') My question is whether there is some way to select distinct key values, e.g. SELECT DISTINCT KEYS____ FROM ____ . Should I come up with a better way to project this property as table perhaps, may using a computed field? Thanks!

Discussion (1)1

Log in or sign up to continue

Timothy Leavitt · Dec 16, 2015

Rather than projecting the property as a table, one option would be to write a custom class query that $orders over the index global, make the column name "KEYS", and make it an [SqlProc]. The SQL would then look like:

SELECT DISTINCT KEYS FROM MyClass_CustomQueryName()

It would be nice if there was a generic solution. My first thought was to add this query to an index class (i.e., one that extends %Library.CacheIndex) and generate <query>Fetch/Execute/Close methods for indices that on property(KEYS), but it seems that queries aren't supported as index members. The <indexName><query>Fetch/Execute/Close methods are generated in the persistent class, but there's no actual class query visible from SQL. Maybe %Library.FunctionalIndex would work if you don't mind reinventing the standard bitmap insert/update/delete, but I suspect that you'd end up losing FOR SOME %ELEMENT in this approach, and would need to replace it with %FIND.

I'll send you what I came up with while investigating - maybe it will provide food for thought.

0 0