1

I've never seen this error code before, nor can I find any documentation on it. Here's the query:

WITH QUERY AS (
    SELECT rrn(T1), blah, bleh, 
        ROW_NUMBER() OVER (order by  bleh desc) AS RN  
    FROM T1 
    WHERE (
        upper(blah) like ('%WEST%') 
     OR upper(bleh) like ('%WEST%')
    ) 
) 
SELECT * FROM QUERY WHERE RN BETWEEN 1 AND 10

The error only happens when upper() is applied.

Error message is "Function not supported for query".

Jeff Siver
  • 7,434
  • 30
  • 32
user1491387
  • 11
  • 3
  • Me neither. DB2 9.7.5 doesn't have it in its documentation. – Jeff Walker Feb 19 '13 at 20:34
  • 2
    Please post the full error message you get. – Ian Bjorhovde Feb 19 '13 at 20:37
  • Error message is "Function not supported for query". Upper works fine on other queries though? – user1491387 Feb 19 '13 at 20:48
  • I'm not familiar with DB2 per se. Are the () around '%WEST%' normal DB2 syntax? Also, have you tried something like WHERE blah LIKE to make sure it's the UPPER that's causing the problem? – Melanie Feb 19 '13 at 21:57
  • Check this out: http://www.ibm.com/developerworks/forums/thread.jspa?threadID=355951 Sounds like your version is v5Rx ? – Darius X. Feb 19 '13 at 22:19
  • This works on V6R1, so updating will cause it to work (not a trivial task, I'll grant). Also, this particular error code shows up in the [message-finder](http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzala%2Frzalafinder.htm); if you include the 'reason code' that would help: my best guess is that it's complaining about the `ROW_NUMBER()` OLAP function. Oh... you're aware that `RRN()` isn't going to return 'consistent' results, if you do things like reorg files, etc, right? Among other problems... Personally, I'd never use it. – Clockwork-Muse Feb 19 '13 at 23:52

0 Answers0