Using ROW NUMBER & UPPER together (SQL Join)

asp_crazy_guy · #1 (**permalink**) 01-27-10, 01:31

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 ?

stolze · #2 (**permalink**) 01-27-10, 03:30

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.

qdiggityd · #3 (**permalink**) 01-27-10, 15:14

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.

qdiggityd · #4 (**permalink**) 01-27-10, 15:17

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?

stolze · #5 (**permalink**) 01-27-10, 15:45

Maybe you can pull-out the UPPER function call to the outer select?

Code:

SELECT ..., UPPER(name)
FROM ( SELECT ..., ROW_NUMBER() OVER (...) FROM ... )
WHERE ...

qdiggityd · #6 (**permalink**) 01-27-10, 15:54

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.

asp_crazy_guy · #7 (**permalink**) 01-28-10, 00:16

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.

tonkuma · #8 (**permalink**) 01-28-10, 07:02

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech