Home » SQL & PL/SQL » SQL & PL/SQL » problem with concatenation and group by functions (11g)
problem with concatenation and group by functions [message #670421] Wed, 04 July 2018 02:18 Go to next message
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 #670422 is a reply to message #670421] Wed, 04 July 2018 03:02 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're not grouping by COM_NUMREF and COM_Type
You're grouping by COM_NUMREF ,floor(rownum/MAX_FIELD_LENGTH),COM_TYPE
and then
COM_NUMREF,COM_TYPE,text

If you want it grouped by COM_NUMREF and COM_Type then do that.
Re: problem with concatenation and group by functions [message #670426 is a reply to message #670421] Wed, 04 July 2018 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

BlackSwan wrote on Mon, 18 June 2018 15:49
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

post CREATE TABLE statements for both tables

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also, if you want to continue to get help, feedback and thank people who spend time to help you.

Re: problem with concatenation and group by functions [message #670428 is a reply to message #670422] Wed, 04 July 2018 04:25 Go to previous messageGo to next message
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 #670429 is a reply to message #670428] Wed, 04 July 2018 04:41 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's complaining about MAX_FIELD_LENGTH obviously - what are you trying to achieve with that?
Re: problem with concatenation and group by functions [message #670430 is a reply to message #670429] Wed, 04 July 2018 04:55 Go to previous messageGo to next message
achraf87
Messages: 6
Registered: May 2012
Location: France
Junior Member

MAX_FIELD_LENGTH for not exceed the 4000 oct allowed for the varchare type .

i know my group by dosn't work because of the MAX_FIELD_LENGTH .

My objectif is the concatenate the COM_TEXTE grouped by COM_NUMREF , and in the second step i will do a inseret into to another table .





Re: problem with concatenation and group by functions [message #670431 is a reply to message #670430] Wed, 04 July 2018 05:04 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
you don't need max field length to limit to 4000 chars, you just substr to 4000 chars.
Or use a clob to avoid the limit.
What happens when the data exceeds the limit? Do you just ignore the rest?

Re: problem with concatenation and group by functions [message #670458 is a reply to message #670431] Thu, 05 July 2018 02:40 Go to previous messageGo to next message
achraf87
Messages: 6
Registered: May 2012
Location: France
Junior Member

Hello ,

I tried the following request , with substr to 4000 chars .

the request works fine with one comment " concatenation and insertion : OK " but when i took all comments the request dosn't work :

insert into COMMENTAIRE_TEST (SELECT COm_type,COM_NUMREF,LISTAGG(dbms_lob.substr(COM_TEXTE,4000,1)) WITHIN GROUP (ORDER BY COM_NUMREF)
FROM commentaire group by COM_NUMREF, COm_type);
--where COM_NUMREF=1416 group by COM_NUMREF, COm_type);
Re: problem with concatenation and group by functions [message #670462 is a reply to message #670458] Thu, 05 July 2018 03:41 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
What does "took all comments" mean?
What does "doesn't work" mean? If you're getting an error you should tell us what it is.
Re: problem with concatenation and group by functions [message #670464 is a reply to message #670428] Thu, 05 July 2018 06:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
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.
Re: problem with concatenation and group by functions [message #670466 is a reply to message #670464] Thu, 05 July 2018 07:41 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
You don't specify your database version, but if your running 12c you can easily increase the maximum size of varchar2 in table data from 4000 to 32k. The change can't be backed out of your database but why would you want to anyway?
Re: problem with concatenation and group by functions [message #670467 is a reply to message #670466] Thu, 05 July 2018 07:51 Go to previous message
achraf87
Messages: 6
Registered: May 2012
Location: France
Junior Member
Hello i use 11g database version
Previous Topic: Stored Procedure / Deletion Conflict
Next Topic: type of constraints
Goto Forum:
  


Current Time: Mon Sep 21 14:30:17 CDT 2020