problem with concatenation and group by functions [message #670421] |
Wed, 04 July 2018 02:18 |
|
achraf87
Messages: 6 Registered: May 2012 Location: France
|
Junior Member |
|
|
I have a problem with the group by function, I can't have the right result
My objectif is to concatenate the Comment " COM_TEXTE" grouped by COM_NUMREF and COM_Type
The group by doesn't function , i have more then line for COM_NUMREF
Please can you help me .
Thany you in advance
select COM_NUMREF ,COM_TYPE, text
--substr( LISTAGG('-' || text || chr(13)) WITHIN GROUP(ORDER BY COM_NUMREF),1,4000)
--LISTAGG(text, '; ')
-- WITHIN GROUP (ORDER BY COM_NUMREF ),
from (
with PRECALC as (select COM_NUMREF ff,
floor(4000/(max(length (to_clob(COM_TEXTE))+LENGTH(',')))) as MAX_FIELD_LENGTH
from commentaire )
select /* PARALLEL(8)*/ COM_NUMREF,COM_TYPE, /* PARALLEL(8)*/ substr( LISTAGG('-' || COM_TEXTE || chr(13)) WITHIN GROUP(ORDER BY COM_NUMREF,MAX_FIELD_LENGTH),1,4000) text --LISTAGG(COM_TEXTE,',') WITHIN GROUP(ORDER BY floor(rownum/MAX_FIELD_LENGTH)) text
from commentaire, PRECALC
--where PRECALC.ff=commentaire.COM_NUMREF
group by COM_NUMREF ,floor(rownum/MAX_FIELD_LENGTH),COM_TYPE
)group by COM_NUMREF,COM_TYPE,text
order by COM_NUMREF
EDIT: fixed the code tags, it's [code] and [/code] not /*code*/
[Updated on: Wed, 04 July 2018 02:58] by Moderator Report message to a moderator
|
|
|
|
|
Re: problem with concatenation and group by functions [message #670428 is a reply to message #670422] |
Wed, 04 July 2018 04:25 |
|
achraf87
Messages: 6 Registered: May 2012 Location: France
|
Junior Member |
|
|
Hello ,
Thank you for your answer
when i do this :
select COM_NUMREF ,COM_TYPE, text
from (
with PRECALC as (select COM_NUMREF ff,
floor(4000/(max(length (to_clob(COM_TEXTE))+LENGTH(',')))) as MAX_FIELD_LENGTH
from commentaire )
select /* PARALLEL(8)*/ COM_NUMREF,COM_TYPE, /* PARALLEL(8)*/ substr( LISTAGG('-' || COM_TEXTE || chr(13)) WITHIN GROUP(ORDER BY COM_NUMREF,MAX_FIELD_LENGTH),1,4000) text
from commentaire, PRECALC
where PRECALC.ff=commentaire.COM_NUMREF
group by COM_NUMREF --,floor(rownum/MAX_FIELD_LENGTH)
)group by COM_NUMREF,COM_TYPE,text
order by COM_NUMREF ;
i have this :
ORA-00979: n'est pas une expression GROUP BY
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
EDIT: fixed code tags - end tag has a backslash [/code]
[Updated on: Wed, 04 July 2018 04:39] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
Re: problem with concatenation and group by functions [message #670464 is a reply to message #670428] |
Thu, 05 July 2018 06:49 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Look at WITH clause:
select COM_NUMREF ff,
floor(4000/(max(length (to_clob(COM_TEXTE))+LENGTH(',')))) as MAX_FIELD_LENGTH
from commentaire
It is using aggregate function MAX while there is no GROUP BY. Add group by COM_NUMREF.
And example to illustrate:
SQL> with t as (
2 select deptno,
3 max(sal) max_sal
4 from emp
5 )
6 select *
7 from t
8 /
select deptno,
*
ERROR at line 2:
ORA-00937: not a single-group group function
SQL>
SY.
|
|
|
|
|