Is there a way to get an array column while using GROUP BY command?
I am trying to get geometry from oracle database as array of coordinates, but get an error when try to use command GROUP BY
SELECT A.C_ID,
A.ID,
COUNT(T.Text),
LISTAGG(T.TEXT, '/// ') WITHIN GROUP (ORDER BY NULL) text,
P.GEOMETRY.sdo_ordinates p_geom
FROM G_P_THEME A
LEFT JOIN G_POLYGON P on P.C_ID=A.C_ID and P.ID=A.ID
LEFT JOIN G_P_TEXT T on P.C_ID=T.C_ID and P.ID=T.ID
WHERE (A.THEME_ID=440 OR A.THEME_ID=47)
Group by A.C_ID, A.ID
Having (COUNT(T.Text) > 1);
it returns an error
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
I know that this is because there is no reference to P.GEOMETRY in GROUP BY command if I add P.GEOMETRY.sdo_ordinates in GROUP BY, result will be
ORA-00932: inconsistent datatypes: expected - got MDSYS.SDO_ORDINATE_ARRAY
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 6 Column: 32
and if there is only P_GEOMETRY in GROUP BY
ORA-22901: cannot compare VARRAY or LOB attributes of an object type
22901. 00000 - "cannot compare VARRAY or LOB attributes of an object type"
*Cause: Comparison of VARRAY or LOB attributes of an object type
was attempted in the absence of a MAP or ORDER method.
*Action: Define a MAP or ORDER method for the object type.
Error at Line: 6 Column: 32
There is probably something that is needed to use in in SELECT to avoid using it in GROUP BY, but couldn't figure out what