1

I have a select that look like this:

SELECT * FROM (SELECT DISTICT COL1, COL2, COL3
               FROM view a WHERE conditions ....
              ) QUERY
WHERE CONDITIONS... LIMIT 20 OFFSET 0

I'm executing this from java and I need this query return an unique id. So i try:

SELECT TRIM(CHAR(HEX(GENERATE_UNIQUE()))) AS GUID, QUERY.* FROM (SELECT DISTICT COL1, COL2, COL3
               FROM view a WHERE conditions ....
              ) QUERY
WHERE CONDITIONS... LIMIT 20 OFFSET 0

This one return an error telling me I can't use this function in that place. If i try:

SELECT * FROM (SELECT DISTINCT TRIM(CHAR(HEX(GENERATE_UNIQUE()))) AS GUID, COL1, COL2, COL3
               FROM view a WHERE conditions ....
              ) QUERY
WHERE CONDITIONS... LIMIT 20 OFFSET 0

I have duplicated rows because it is like I execute query wihout DISTINCT

Does anyone know a way to do it?

I don't know DB2 version (I have tried all solutions from How to check db2 version )

Juan
  • 544
  • 6
  • 20

1 Answers1

1

If a numeric id would do, how about just using row_number():

SELECT CAST(ROW_NUMBER() OVER (ORDER BY COL1, COL2, COL3) as VARCHAR(255)) as unique_id,
       QUERY.*
FROM (SELECT DISTICT COL1, COL2, COL3
      FROM view a
      WHERE conditions ....
     ) QUERY
WHERE CONDITIONS...
LIMIT 20 OFFSET 0
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • My feeling is that the OP wants to generate a UUID or something similar which can never be duplicated (in theory). – Tim Biegeleisen Jan 18 '19 at 11:34
  • I get an error from java Exception [EclipseLink-6044] (Eclipse Persistence Services - 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.QueryException Exception Description: The primary key read from the row [ArrayRecord( => 1 ... ) I'm going to take a look to this error. But thank you very much. – Juan Jan 18 '19 at 11:56
  • Finally it works, I also have some problems with java ContructorResult. thank you – Juan Jan 18 '19 at 12:35
  • For future readers, check out this UUID generation answer [here](https://stackoverflow.com/questions/62350083/db2-generate-guid-uuid). – griv Aug 02 '23 at 23:33