Hi all,
I try using case statement on select like that
SELECT
xmlserialize(DOCUMENT
xmlroot(XMLELEMENT(kdpwdocument,XMLATTRIBUTES(
'xxxxxxxx' AS sndr,
'xxxxxxxx' AS rcvr,
'xxxxxxxx' AS "xmlns",
'xxxxxxxx' AS "xmlns:xsi",
'xxxxxxxx' AS "xsi:schemaLocation"
),
CASE WHEN REPO_STATUS=1 THEN
xmlagg(
XMLELEMENT("regio",
XMLELEMENT("Inf",
XMLELEMENT("Id",
XMLELEMENT("PmryId",'1111'),
XMLELEMENT("ScndryId",'1111')
)
)
)
)
ELSE
xmlagg(
XMLELEMENT("regio",
XMLELEMENT("Inf",
XMLELEMENT("Id",
XMLELEMENT("PmryId",'0000'),
XMLELEMENT("ScndryId",'0000')
)
)
)
)
END
) ,
VERSION '1.0" encoding="UTF-8'
) AS CLOB)
FROM REPO
WHERE 1=1
;
but i'm getting error:
00937. 00000 - "not a single-group group function"
Any idea how I can change my query to get results? When I'm using group by repo_status, I'm getting two rows, but that isn't a good result for me