Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2008
    Posts
    10

    Unanswered: using upper with OLAP function

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2008
    Posts
    10
    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

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Dec 2008
    Posts
    10
    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.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •