|Error ORA-01722: Invalid use of Number in Select with Xmlagg / Xmlelement [message #664235]
||Sun, 09 July 2017 03:33
Registered: July 2017
I am getting the above error for the following query - only after it runs already for about 30 min. |
Since I only get the error after this time I was wondering if it is caused by something towards the end of the query, e.g. the .EXTRACT ?
I tried using TO_CHAR and NVL for all items in the Xmlelement which didn't help either.
However, when I use the same structure (Rtrim / Xmlagg / Xmlelement) separately for columns 2 to 6 then it works fine (it just doesn't help me that way).
Listagg works too but doesn't allow enough characters.
Can someone please tell me what I am doing wrong here ?
ORA-01722 ... invalid use of a NUMBER.
a.column1 AS ID
e, 'Column2: ' || b.column2 ||
'Column3: ' || SUBSTR(c.column3, 1, 50) || '...' ||
'Column4: ' || b.column4 ||
'Column5: ' || TO_CHAR(b.column5, 'FM9,990.00') ||
'Column6: ' || TO_CHAR(b.column6, 'FM9,990.00') ||
) ORDER BY b.column2
) AS AD
, TO_CHAR(b.column7, 'FM9,990.00') AS GN
, TO_CHAR(b.column8, 'FM9,990.00') AS GU
, TO_CHAR((b.column7 + b.column8), 'FM9,990.00') AS GB
/* ... */
Could it be possible that the problem here is that I use ',' as the EXTRACT separator but might also have commas somewhere within my column values ?
Any help is much appreciated,