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

@xQbert I'm using an oracle database accessed via ORA_ODBC driver in websphere MQ/esql. The query for array2 is accessing the oracle database - figured it might be a relevant tag. — bbedward, Jan 29 '16 at 16:07
@agent5566 No it does not support group by/order by. I'm thinking after the select there has to be some logic/sorting to get the distinct IDs, but I'm not sure the best way in esql. I think I will re-word my question a bit as I have since changed the code slightly - to make it less confusing. — bbedward, Feb 01 '16 at 14:25
I don't know what this question has in it that relates to WebSphere MQ. There is no ESQL in MQ. Do you mean Message Broker? If yes, suggest you tag accordingly to bring it to the attentions of MB experts - more likely to get an answer that way. — Morag Hughson, Feb 03 '16 at 10:13

score 2 · Accepted Answer · answered Feb 04 '16 at 06:31

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, ...)

bbedward · Answer 2 · 2016-02-09T19:03:30.867

My work around ended up not doing select distinct or sorting at all but another work around.

Basically I iterate through the entire array of ERROR_CODEs, then I query for the ID that corresponds to the error_code, then I select count(*) in a table I insert them to.

This works for my particular application only because I insert the ID/Issue pairs.

Basically it looks like:

for x as errs.Error[] do
    declare db row;
    set db.rows[] = passthru('select ID from my_static_map_table where error_code = ?;' values(x.Code));

    declare db2 row;
    set db2.rows[] = passthru('select count(*) from my_table_2 where guid = ? and id = ?;' values(guid, db.ID));

    if db2.COUNT == 0 then
        -- Here I do an insert into my_table_2 with ID and a few other values
    end if;
end for;

Not really a proper answer, but it works for my specific application. Basically loop through every error code and select one at a time, rather than sending in the entire array. Then doing an insert into another database while avoiding duplicates by another select to see if it's already been inserted.

I'll still wait a week to see if there's a better answer and accept that one.

UPDATE

I've changed my code to match Attila's solution - which is much better and what I was looking for originally

Only thing I will add is my function that formats the error codes - which is really simple:

create function FlattenErrorCodesArray(in err row) returns char begin
    declare idx int 1;
    declare ret char;

    for x as errs.Error[] do
        if idx = 1 then
            set ret = '(' || cast(x.Code as char);
        else
            set ret = ret || ',' || cast(x.Code as char);
        end if;
        set idx = idx + 1;
    end for;
    set ret = ret || ')';
end;

How to select distinct in esql?

2 Answers2