|
Problem IT35150
|
Status: Closed |
UNEXPECTED SQL0538 WITH NTILE BUILTIN FUNCTION
|
| product: |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 |
| Problem description: |
NTILE is non-deterministic in that the same value may fall into
multiple TILES depending on data set and # tiles choosen.
However for a given set of data and sort order the rows will
fall into the same TILES and thus deterministic from a SQL
compiler perspective.
Example:
db2 "select firstnme, lastname, salary, ntile(3) over (order by
salary) salary_ntile from employee"
above query works but when NTILE is used in a CASE expression it
produces
non-deterministic error SQL0583N
db2 "select firstnme, lastname, salary, case ntile(3) over
(order by salary) when 1 then 'a' when 2 then 'b' when 3 then
'c' end as salary_ntile from employee" |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Db2V11.1.4.5 or lower *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Update to DB2V11.1.4.6 or higher *
**************************************************************** |
| Local Fix: |
|
| Solution |
|
| Workaround |
****************************************************************
* USERS AFFECTED: *
* Db2V11.1.4.5 or lower *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Update to DB2V11.1.4.6 or higher *
**************************************************************** |
| Comment |
Problem Fixed in DB2V11.1.4.6 |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 03.12.2020
18.02.2021
18.02.2021 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
