Error ORA-01722: Invalid use of Number in Select with Xmlagg / Xmlelement [message #664235] |
Sun, 09 July 2017 03:33 |
|
keewee279
Messages: 1 Registered: July 2017
|
Junior Member |
|
|
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 ?
Error message:
ORA-01722 ... invalid use of a NUMBER.
My query:
SELECT
a.column1 AS ID
, RTRIM
(
XMLAGG
(
XMLELEMENT
(
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
).EXTRACT('//text()'), ','
) 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
FROM
/* ... */
Update:
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,
Mike
|
|
|
Re: Error ORA-01722: Invalid use of Number in Select with Xmlagg / Xmlelement [message #664238 is a reply to message #664235] |
Sun, 09 July 2017 06:05 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
Quote: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 ?
Yes it is possible.
With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects and insert statements so that we will be able work to reproduce what you have.
|
|
|