OraFAQ Forum: SQL & PL/SQL » problem with concatenation and group by functions

Home » SQL & PL/SQL » SQL & PL/SQL » problem with concatenation and group by functions (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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*/

Attachment: Capture oracle.PNG
(Size: 29.51KB, Downloaded 1148 times)

[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

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: problem with concatenation and group by functions [message #670426 is a reply to message #670421]

Wed, 04 July 2018 03:18

Michel Cadot
Messages: 68641
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.

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 #670429 is a reply to message #670428]

Wed, 04 July 2018 04:41

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

It's complaining about MAX_FIELD_LENGTH obviously - what are you trying to achieve with that?

Report message to a moderator

Re: problem with concatenation and group by functions [message #670430 is a reply to message #670429]

Wed, 04 July 2018 04:55

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 .

Report message to a moderator

Re: problem with concatenation and group by functions [message #670431 is a reply to message #670430]

Wed, 04 July 2018 05:04

cookiemonster
Messages: 13920
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?

Report message to a moderator

Re: problem with concatenation and group by functions [message #670458 is a reply to message #670431]

Thu, 05 July 2018 02:40

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);

Report message to a moderator

Re: problem with concatenation and group by functions [message #670462 is a reply to message #670458]

Thu, 05 July 2018 03:41

cookiemonster
Messages: 13920
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.

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: 3273
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.

Report message to a moderator

Re: problem with concatenation and group by functions [message #670466 is a reply to message #670464]

Thu, 05 July 2018 07:41

Bill B
Messages: 1971
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?

Report message to a moderator

Re: problem with concatenation and group by functions [message #670467 is a reply to message #670466]

Thu, 05 July 2018 07:51

achraf87
Messages: 6
Registered: May 2012
Location: France

Junior Member

Hello i use 11g database version

Report message to a moderator

Previous Topic:	Stored Procedure / Deletion Conflict
Next Topic:	type of constraints

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 14:11:08 CDT 2024