using upper with OLAP function

ramanay · #1 (**permalink**) 01-26-09, 11:26

hi,
I have a query like

select * from( SELECT ROW_NUMBER() OVER(ORDER BY SNum) AS row,SNum

from atm.asnum

where SNum<> ''
and UPPER(SNum)=UPPER('atal'))

where row >=2 and row <=20

I get the error SQL0255- function not supported.
Is there any other way to use the upper with row_number . I tried translate as well but same error.

stolze · #2 (**permalink**) 01-26-09, 13:07

Which DB2 version and platform are you using? I am asking because SQL0255 is not a valid error message on DB2 LUW.

What have you tried so far to narrow down the problem?

ramanay · #3 (**permalink**) 01-26-09, 13:15

Its a V5R4 on I5/OS.

I tried to run the SELECT SNum

from atm.asnum

where SNum<> ''
and UPPER(SNum)=UPPER('atal')
It runs fine.

Thanks
-R

Peter.Vanroose · #4 (**permalink**) 01-26-09, 14:30

Quote:

Originally Posted by ramanay

select * from( SELECT ROW_NUMBER() OVER(ORDER BY SNum) AS row,SNum
from atm.asnum
where SNum<> ''
and UPPER(SNum)=UPPER('atal'))
where row >=2 and row <=20

I get the error SQL0255- function not supported.

Could it be that your DB2 version does not support the ROW_NUMBER() function?
If so, the following would be "almost as good" (apart from an extra first row):

Code:

SELECT SNum FROM atm.asnum
WHERE UPPER(SNum)=UPPER('atal')
ORDER BY SNum
FETCH FIRST 20 ROWS ONLY

ramanay · #5 (**permalink**) 01-26-09, 14:48

Hi,
The row_number function worked fine prior to using the upper.
Its the combination which of row_number and upper which fails.Both work fine independent of each other. And i have to use row_number as it is required for paging to retrieve specified number of records with startCount and endCount.

Peter.Vanroose · #6 (**permalink**) 01-26-09, 14:59

Try the following:

Code:

select *
from ( select ROW_NUMBER() OVER(ORDER BY SNum) AS row, SNum
       from   atm.asnum
       where  UPPER(SNum)=UPPER('atal')) t
where row between 2 and 20

(I.e., I added the alias "t" for the nested table expression.)

B.t.w., are you sure that SNum is a textual column?
Maybe you need to explicitly cast it to VARCHAR(64) or something before applying UPPER to it.

Internet Services

Web Development

Digital Marketing

Consumer Tech