How to select distinct in esql?

Question

I have a subflow in esql (IBM Websphere Message Broker) where I need to achieve something similar to select distinct functionality.

Some background: I have a table in an Oracle database group_errcode_ref. This table is pretty much a fixed link/mapping of ERROR_CODE and ID. ERROR_CODE is unique, but ID can be duplicated. For example error code 4000 and 4001 can both be linked to ID 1.

In my esql subflow, I have an array of error codes that varies based on the current data coming into the flow.

So what I need to do is I need to take the input error code array, and select the ID for all the error codes in the array from my table group_errcode_ref

What I have now:

declare db rows;
set db.rows[] = (select d.ID from Database.group_errcode_ref as d where d.ERROR_CODE in (select D from errCodes.Code[] as D);

errCodes is the array of error codes from the input. row is an array of all IDs that correspond to the error codes.

This is fine, but I want to remove duplicates from the db.rows[] array.

I'm not certain the best way to do this in esql, but it does not support distinct. group by, or order by

Answer 1

If you are using the PASSTHRU statement, then all the functionality of your database manager is supported, so distinct as well.

The only thing you have to overcome is that you cannot directly mix database and messagetree queries in PASSTHRU, everything you pass to it goes directly to the database.

So your original solution would look something like this:

set db.rows[] = PASSTHRU 'select distinct d.ID from SCHEMA.group_errcode_ref as d where d.ERROR_CODE in ' || getErrorCodesFromInput(errCodes) TO Database.DSN1;

Here getErrorCodesFromInput is a function that returns character, which contains the error codes in your input, formatted correctly for the query, e.g. (ec1, ec2, ...)