Hello All,
If I use NULLIF function for CLOB field, I am facing ORA-00932 error as below:
with T1 as (
select
(
SELECT
RTRIM(XMLCAST(
XMLAGG(
XMLELEMENT(E,' '||x.column\_name||' as '||x.column\_alias,','||chr(10))
ORDER BY ORD\_POS
) AS CLOB
),','||CHR(10))
FROM XMLTABLE (
'/functionalView/columns/column'
PASSING A.VIEWDEF
COLUMNS
ORD\_POS FOR ORDINALITY,
COLUMN\_NAME VARCHAR2(30) PATH '@name',
COLUMN\_ALIAS VARCHAR2(30) PATH '@columnAlias'
) X
) SELECT\_CLAUSE
FROM XML_DATA A
WHERE ROWNUM=1
)
SELECT
nullif(select_clause,'COL1')
from T1;
However above sql is working fine without nullif function as below:
with T1 as (
select
(
SELECT
RTRIM(XMLCAST(
XMLAGG(
XMLELEMENT(E,' '||x.column\_name||' as '||x.column\_alias,','||chr(10))
ORDER BY ORD\_POS
) AS CLOB
),','||CHR(10))
FROM XMLTABLE (
'/functionalView/columns/column'
PASSING A.VIEWDEF
COLUMNS
ORD\_POS FOR ORDINALITY,
COLUMN\_NAME VARCHAR2(30) PATH '@name',
COLUMN\_ALIAS VARCHAR2(30) PATH '@columnAlias'
) X
) SELECT\_CLAUSE
FROM XML_DATA A
WHERE ROWNUM=1
)
SELECT
select_clause
from T1;
If I replace xmlagg as clob to listagg then there is no issue as below:
with T1 as (
select
(
select listagg(x.column\_name||' as '||x.column\_alias, ', ') within group (order by ord\_pos)
from xmltable (
'/functionalView/columns/column'
passing a.viewdef
columns
ord\_pos for ordinality,
column\_name varchar2(30) path '@name',
column\_alias varchar2(30) path '@columnAlias'
) x
) select\_clause
FROM XML_DATA A
WHERE ROWNUM=1
)
SELECT
nullif(select_clause, 'col1')
from T1;
I want to use xmlagg as CLOB only and is there a way that I can manage error due to nullif clause.
Thanks,
Rajneesh