DB2 RowNumber equality check in When clause

Question

I am starter on DB2.

when I develop query I met a problem with error code SQLCODE=-120, SQLSTATE=-42903

under query is that.

SELECT PHYSICAL_NAME||''
  FROM DEV_RESOURCE
  WHERE RESOURCE_ID = '40069684046725203038312337393001'
  AND ROWNUMBER() OVER() = 1 --This line is problem.
  WITH UR

I think compare ROWNUMBER() OVER() and 1 is problem. How to fix it?

In oracle than query(ROWNUMBER() OVER() -> ROWNUM) is work.

Answer 1

There are a couple options. If you just want to return a single row, you could do:

SELECT PHYSICAL_NAME||''
  FROM DEV_RESOURCE
  WHERE RESOURCE_ID = '40069684046725203038312337393001'
  FETCH FIRST ROW ONLY
  WITH UR

DB2 has the above syntax, but also understands MySQL syntax (LIMIT). Another option is to rewrite the query like this:

SELECT PHYSICAL_NAME||''
  FROM (select rownumber() over() as rn, DR.* from 
  DEV_RESOURCE DR 
  WHERE RESOURCE_ID = '40069684046725203038312337393001')
  WHERE RN=1
  WITH UR

There are more options, depending on what you want to do.