Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Posts
    73

    Unanswered: Using ROW NUMBER & UPPER together (SQL Join)

    Hi just migrated from DB2 for windows to ISeries V5 R4 and lot of queries are just not running altogether. I cant run this on ISeries although it was running on db2 for windows.
    Code:
    SELECT * from (select row_number() over (ORDER BY GENLAYERSSRNO ASC) AS s_no, a.GENMAXAMT,a.GENMINAMT
    ,a.GENLAYERSSRNO,
    
    	UPPER(b.GENCURRCODE
    )
    	FROM TST.UWFTRTYLAYER
     as a
    
    	INNER JOIN TST.UWFTRTYSTP as b
     on a.genfortreatysrno=b.genfortreatysrno
     where a.genfortreatysrno=74) t
    ;
    [SQL0255] Function not supported for query. Cause . . . . . : The reason code is 6:
    The reason code is 6: -- Code 1 -- Scalar subselects and lateral correlation from a nested table expression are not allowed with distributed files. -- Code 2 -- Error occurred while using a temporary distributed file. -- Code 3 -- EXCEPT or INTERSECT not supported for this query. -- Code 4 -- A sequence reference is not supported with distributed files. -- Code 5 -- A recursive common table expression is not supported for this query. -- Code 6 -- An OLAP function is not supported for this query. -- Code 7 -- ORDER OF is not supported for this query. -- Code 8 -- Scalar fullselect is not supported for this query. -- Code 9 -- A distributed file is being processed in a multi-thread capable job or this is not the initial thread. Recovery . . . : A list of corrective actions follow: -- If code 1, change the query so it does not use scalar subselects or correlation from a nested table expression. -- If code 2, see the previous messages for more information. -- If code 3, remove EXCEPT or INTERSECT from the query. -- If code 4, remove the sequence reference from the query. -- If code 5, remove the recursive common table expression from the query. -- If code 6, remove the OLAP function from the query. -- If code 7, remove the ORDER OF from the query. -- If code 8, respecify the query without a fullselect. -- If code 9, do not use multiple threads to run the query.
    Seems like upper and join dont go together. Any clue guys ?
    Last edited by asp_crazy_guy; 01-27-10 at 02:35.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    DB2 for iSeries is a different product (different code) than DB2 for LUW. So you should check the respective manuals to see if your version allows the functions you are using and which restrictions are imposed. From the error message, I gather that it is not possible.

    I don't think that your problem has anything to do with UPPER. UPPER is not an OLAP function - ROW_NUMBER() is.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2010
    Posts
    3
    An OLAP function isn't allowed with a UPPER function in v5r4

    The SQL Reference R540 states the following :

    "Chapter 2. Language elements" -> "Expressions" -> OLAP specifications
    ....

    An OLAP specification is not allowed if the query specifies:
    - lateral correlation
    - a sort sequence
    - an operation that requires CCSID conversion

    - a LOWER, TRANSLATE, or UPPER scalar function

    - a UTF-8 or UTF-16 argument in a CHARACTER_LENGTH, POSITION, or
    SUBSTRING scalar function
    - a table function
    - a distributed table
    - a table with a read trigger
    - a table referenced directly or indirectly in the fullselect must not
    be a DDS-created logical file
    - a logical file built over multiple physical file members.

    In v6r1 there are fewer restrictions:

    An OLAP specification is not allowed if the query specifies:
    - a distributed table,
    - a table with a read trigger,
    - a table referenced directly or indirectly in the fullselect must not be a DDS-created logical file, or
    - a logical file built over multiple physical file members.

  4. #4
    Join Date
    Jan 2010
    Posts
    3
    I'm having the same problem - the above is what I got from IBM....just wondering if anyone out there has another way to do pagination while checking upper/lower on fields without using an olap function?

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Maybe you can pull-out the UPPER function call to the outer select?
    Code:
    SELECT ..., UPPER(name)
    FROM ( SELECT ..., ROW_NUMBER() OVER (...) FROM ... )
    WHERE ...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jan 2010
    Posts
    3
    That was exactly my thought but got same error. Even created a logical/view with all text fields sent to lower/upper case, tried to query that and add a row_number() over() and still got an error..... my only option at this point is to upgrade to v6r1 or lower/upper case all inputted values as they go into the table.

  7. #7
    Join Date
    Mar 2009
    Posts
    73

    Thumbs up

    Thanks for the reply guys. Seems we have to take upper out of our query to get it working for the time being till any workaround is found.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although, this may also fail, another idea is to create UDF.
    Like this...
    (Tested on DB2 9.7 on Windows/XP.)
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE FUNCTION upper_udf(in_string VARCHAR(255) )
     RETURNS VARCHAR(255)
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
    BEGIN ATOMIC
    
    DECLARE return_string VARCHAR(255);
    DECLARE k INTEGER DEFAULT 1;
    
    SET return_string = SUBSTR(in_string, 1, 0);
    
    WHILE k <= LENGTH(in_string) DO
       SET return_string =
           return_string
           || CASE SUBSTR(in_string, k, 1)
              WHEN 'a' THEN 'A' WHEN 'b' THEN 'B' WHEN 'c' THEN 'C'
              WHEN 'd' THEN 'D' WHEN 'e' THEN 'E' WHEN 'f' THEN 'F'
              WHEN 'g' THEN 'G' WHEN 'h' THEN 'H' WHEN 'i' THEN 'I'
              WHEN 'j' THEN 'J' WHEN 'k' THEN 'K' WHEN 'l' THEN 'L'
              WHEN 'm' THEN 'M' WHEN 'n' THEN 'N' WHEN 'o' THEN 'O'
              WHEN 'p' THEN 'P' WHEN 'q' THEN 'Q' WHEN 'r' THEN 'R'
              WHEN 's' THEN 'S' WHEN 't' THEN 'T' WHEN 'u' THEN 'U'
              WHEN 'v' THEN 'V' WHEN 'w' THEN 'W' WHEN 'x' THEN 'X'
              WHEN 'y' THEN 'Y' WHEN 'z' THEN 'Z'
              ELSE SUBSTR(in_string, k, 1)
              END ;
       SET k = k + 1;
    END WHILE;
    
    RETURN return_string;
    
    END@
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

Posting Permissions

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