Verification of Result in ESQL

Question

Here's the situation: I run a query that gives me one or many rows as a result in an array.

Like:

SET db = PASSTHRU('SELECT GUID,CONTROLNBR FROM TRANSACTION WHERE GUID > ? AND CONTROLNBR > ?' values(maxGuid,maxControlNbr);

That works fine but I want to verify the following:

Any result that has duplicate CONTROLNBR's must have the same GUID

So if my result set has something like this:

   CONTROLNBR | GUID
   --------------------
      5       | 123abc
      5       | 123abc

this is entirely valid, however this I need to return an error on:

   CONTROLNBR | GUID
   --------------------
      5       | 123abc
      5       | abc123

I'm not sure the best way to test the result like this in ESQL/SQL.

Answer 1

To find bad duplicates you can run this query in SQL, not sure if that works in ESQL:

SELECT CONTROLNBR, COUNT(DISTINCT GUID) as CountGUID
FROM myTable 
GROUP BY CONTROLNBR
HAVIGNG COUNT(DISTINCT GUID) > 1