Under rare scenarios, DB2 might produce SQL0901N error message
if the following conditions are true:
1. The query contains a group by clause
2. The output column list contains an expression
3. An order by clause is defined in the same subselect as the
expression
4. The order by clause contains a aggregation function of the
expression in 3.
eg.
SELECT
( CASE WHEN flag = 1 THEN ID2 ELSE '' END) AS ID
FROM
(
SELECT
ID as ID2,
1 as flag
FROM temp
) a
GROUP BY
( CASE WHEN flag = 1 THEN ID2 ELSE '' END)
ORDER BY
min(ID) desc;
Error message:
SQL0901N The SQL statement failed because of a non-severe
system error.
Subsequent SQL statements can be processed. (Reason "QNC in
column function
references complex expression".) SQLSTATE=58004
db2diag.log messages:
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
MESSAGE : ZRC=0x8031000F=-2144272369=SQLNN_E_AMBIG
"Conflicting or ambiguous elements in the command or
an
internal object"
DATA #1 : String, 62 bytes
An unexpected error was detected during statement compilation.
DATA #2 : Boolean, 1 bytes
true
DATA #3 : Boolean, 1 bytes
false
DATA #4 : Boolean, 1 bytes
false
DATA #5 : Boolean, 1 bytes
false
DATA #6 : Hex integer, 4 bytes
0x00000000
DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 52
sqlerrmc: QNC in column function references complex expression
sqlerrp : SQLNQ002
sqlerrd : (1) 0x801A006D (2) 0x00000000 (3)
0x00000000
(4) 0x00000000 (5) 0xFFFFFEDE (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
DATA #8 : Hex integer, 4 bytes
0x00000040
DATA #9 : String, 244 bytes
Compiler error stack for rc = -2144272369:
sqlnn_cmpl[300]
sqlnp_main[250]
sqlnp_parser[510]
sqlnp_smactn[100]
sqlnq_sem_function_call[100]
sqlnq_proc_agf[290] |
Add the aggregate expression in the order by clause into the
select list.
eg. rewritten query:
select ID
from (
SELECT
( CASE WHEN flag = 1 THEN ID ELSE '' END) AS ID ,
min (( CASE WHEN flag = 1 THEN ID ELSE '' END)) as
min_ID
FROM
(
SELECT
ID as ID2,
1 AS flag
FROM
CAPO_IDGCBA.RDE_MONITOR_VIEW rqi
) a
GROUP BY
( CASE WHEN flag = 1 THEN ID ELSE '' END)
)
ORDER BY
min_ID desc; |
