Document Type | Issue Resolution
Category | App Development
Applicable Product Versions | 6FS06, 6FS07, 6FS07PS, 7FS01, 7FS02, 7FS02PS
Error Code | 11005
Document Number | TDETS004
Issue
If a non-numeric NULL value is input into the offset parameter of the LAG and LEAD built-in functions, a TBR-11005 error occurs instead of a query result.
TBR-11005: Specified value '-E+125' is invalid for mathematical function argument(s). at line 1, column 45 of null: SELECT TABLE_NAME, NUM_ROWS, LAG(NUM_ROWS, NULL, 0) --Executed Query 1: Executing with an explicit offset parameter NULL value SQL> SELECT TABLE_NAME, NUM_ROWS, LAG(NUM_ROWS, NULL, 0) OVER (PARTITION BY OWNER ORDER BY NUM_ROWS) AS LROW FROM DBA_TABLES; --Executed Query 2: Executing with offset parameter as NULL due to a value not matching DECODE expression SQL> SELECT TABLE_NAME, NUM_ROWS, LAG(NUM_ROWS, DECODE(OWNER, 'SYS', 1, 'TIBERO',2), 0) OVER (PARTITION BY OWNER ORDER BY NUM_ROWS) AS LROW FROM DBA_TABLES;
LAG(expr, offset, default)
The LAG function is an analytic function that allows referencing multiple rows within a single table simultaneously without self-joining. It accesses the value of the row located before the current row by the specified offset.
LEAD(expr, offset, default)
Similarly, the LEAD function is an analytic function that allows referencing multiple rows within the same table without a separate join. It accesses the value of the row located after the current row by the specified offset.
Cause
To prevent negative values from being input into the offset parameter, a logic comparing offset and number_zero was used, but an error occurred due to comparing NULL with number_zero.
Solutions
Modify the query to handle NULL values separately.
--Workaround to output the current ROW value when NULL
SELECT TABLE_NAME, NUM_ROWS,
LAG(NUM_ROWS, NVL(NULL,0), 0) OVER (PARTITION BY OWNER ORDER BY NUM_ROWS) AS LROW
FROM DBA_TABLES;
--Query separating data that matches DECODE and data that does not (for data not matching DECODE, the default third parameter is output)
SELECT TABLE_NAME, NUM_ROWS,
LAG(NUM_ROWS, DECODE(OWNER, 'SYS', 1, 'TIBERO',2), 0) OVER (PARTITION BY OWNER ORDER BY NUM_ROWS) AS LROW
FROM DBA_TABLES
WHERE OWNER IN ('SYS','TIBERO')
UNION ALL
SELECT TABLE_NAME, NUM_ROWS,
0 AS LROW
FROM DBA_TABLES
WHERE OWNER NOT IN ('SYS','TIBERO')
;