Home » SQL & PL/SQL » SQL & PL/SQL » needed order by in concat_all() function
needed order by in concat_all() function [message #236131] Wed, 09 May 2007 02:46 Go to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member
Hi,

I am using concat_all function

but i needed cancatinated column in sorted order
please see the o/p below


select abo_rdo_code,concat_all(concat_expr(country_name, ', ')) country_name
from temp1
group by abo_rdo_code

o/p -


ABO_RDO_CODE COUNTRY_NAME
------------- -------------------
1 United States, Canada
2 Fiji, New Zealand, Australia, French Polynesia, Papua New Guinea



I needed country_name in order by asc
Like ...


ABO_RDO_CODE COUNTRY_NAME
------------- -------------------
1 Canada, United States
2 Australia, Fiji, French Polynesia, New Zealand, Papua New Guinea


Please help me ASAP


Prasad


Re: needed order by in concat_all() function [message #236133 is a reply to message #236131] Wed, 09 May 2007 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just modify the code to include a sort either at each step (odciaggregatemerge) or at the end (odciaggregateterminate).
You can also instead of building the string at each step, fill an array and then in the end sort it and create the string.

Regards
Michel
Re: needed order by in concat_all() function [message #236137 is a reply to message #236133] Wed, 09 May 2007 03:04 Go to previous messageGo to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member
but code giving following compilation error please help

--CONCAT_EXPR

create or replace TYPE "CONCAT_EXPR" AS OBJECT (
str VARCHAR2 (4000),
del VARCHAR2 (4000),
MAP MEMBER FUNCTION f_map RETURN VARCHAR2)
Type created

/
create or replace TYPE BODY concat_expr AS
MAP MEMBER FUNCTION f_map
RETURN VARCHAR2
IS
BEGIN
RETURN str;
END f_map;
END;
Type body created

--concat_all_ot

create or replace TYPE "concat_all_ot" AS OBJECT (
str VARCHAR2 (4000),
del VARCHAR2 (4000),

STATIC FUNCTION odciaggregateinitialize (
sctx IN OUT concat_all_ot)
RETURN NUMBER,

MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT concat_all_ot,
ctx IN concat_expr)
RETURN NUMBER,

MEMBER FUNCTION odciaggregateterminate (
SELF IN concat_all_ot,
returnvalue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,

MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT concat_all_ot,
ctx2 concat_all_ot)
RETURN NUMBER)
Warning: Type created with compilation errors

/
create or replace TYPE BODY concat_all_ot
AS
STATIC FUNCTION odciaggregateinitialize (
sctx IN OUT concat_all_ot)
RETURN NUMBER
IS
BEGIN
sctx := concat_all_ot (NULL, NULL);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT concat_all_ot,
ctx IN concat_expr)
RETURN NUMBER
IS
BEGIN
IF SELF.str IS NOT NULL THEN
SELF.str := SELF.str || ctx.del;
END IF;
SELF.str := SELF.str || ctx.str;
RETURN odciconst.success;
END;

MEMBER FUNCTION odciaggregateterminate (
SELF IN concat_all_ot,
returnvalue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER
IS
BEGIN
returnvalue := SELF.str;
RETURN odciconst.success;
END;

MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT concat_all_ot,
ctx2 IN concat_all_ot)
RETURN NUMBER
IS
BEGIN
IF SELF.str IS NOT NULL THEN
SELF.str := SELF.str || SELF.del;
END IF;
SELF.str := SELF.str || ctx2.str;
RETURN odciconst.success;
END;
END;
Warning: Type body created with compilation errors

create or replace FUNCTION concat_all (
ctx IN concat_expr)
RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
AGGREGATE USING concat_all_ot;
Warning: Function created with compilation errors

Re: needed order by in concat_all() function [message #236143 is a reply to message #236137] Wed, 09 May 2007 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Correct the error.

Read and apply How to format your posts.

Regards
Michel
Re: needed order by in concat_all() function [message #236181 is a reply to message #236143] Wed, 09 May 2007 04:29 Go to previous messageGo to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member

michel,

code was same as you sent me in the link = CODE (IN first reply)



Regards,
Prasad
Re: needed order by in concat_all() function [message #236185 is a reply to message #236181] Wed, 09 May 2007 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How can I know what error you have?

Regards
Michel
Re: needed order by in concat_all() function [message #236188 is a reply to message #236143] Wed, 09 May 2007 04:36 Go to previous messageGo to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member
 
--CONCAT_EXPR 

create or replace TYPE "CONCAT_EXPR" AS OBJECT (
  str VARCHAR2 (4000),
  del VARCHAR2 (4000),
  MAP MEMBER FUNCTION f_map RETURN VARCHAR2);
/


create or replace TYPE BODY concat_expr AS
  MAP MEMBER FUNCTION f_map
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN str;
  END f_map;
END;
/


--concat_all_ot 

create or replace TYPE "concat_all_ot"  AS OBJECT (
  str VARCHAR2 (4000),
  del VARCHAR2 (4000),

  STATIC FUNCTION odciaggregateinitialize (
  sctx IN OUT concat_all_ot)
  RETURN NUMBER,

  MEMBER FUNCTION odciaggregateiterate (
  SELF IN OUT concat_all_ot,
  ctx IN concat_expr)
  RETURN NUMBER,

  MEMBER FUNCTION odciaggregateterminate (
  SELF IN concat_all_ot,
  returnvalue OUT VARCHAR2,
  flags IN NUMBER)
  RETURN NUMBER,

  MEMBER FUNCTION odciaggregatemerge (
  SELF IN OUT concat_all_ot,
  ctx2 concat_all_ot)
  RETURN NUMBER);
/


create or replace TYPE BODY concat_all_ot
AS
  STATIC FUNCTION odciaggregateinitialize (
  sctx IN OUT concat_all_ot)
  RETURN NUMBER
  IS
  BEGIN
  sctx := concat_all_ot (NULL, NULL);
  RETURN odciconst.success;
  END;
  MEMBER FUNCTION odciaggregateiterate (
  SELF IN OUT concat_all_ot,
  ctx IN concat_expr)
  RETURN NUMBER
  IS
  BEGIN
  IF SELF.str IS NOT NULL THEN
    SELF.str := SELF.str || ctx.del;
  END IF;
  SELF.str := SELF.str || ctx.str;
  RETURN odciconst.success;
  END;

  MEMBER FUNCTION odciaggregateterminate (
  SELF IN concat_all_ot,
  returnvalue OUT VARCHAR2,
  flags IN NUMBER)
  RETURN NUMBER
  IS
  BEGIN
  returnvalue := SELF.str;
  RETURN odciconst.success;
  END;

  MEMBER FUNCTION odciaggregatemerge (
  SELF IN OUT concat_all_ot,
  ctx2 IN concat_all_ot)
  RETURN NUMBER
  IS
  BEGIN
  IF SELF.str IS NOT NULL THEN
    SELF.str := SELF.str || SELF.del;
  END IF;
  SELF.str := SELF.str || ctx2.str;
  RETURN odciconst.success;
  END;
END;
/


create or replace FUNCTION concat_all (
  ctx IN concat_expr)
  RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
  AGGREGATE USING concat_all_ot;
/

Re: needed order by in concat_all() function [message #236192 is a reply to message #236185] Wed, 09 May 2007 04:41 Go to previous messageGo to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member
--CONCAT_EXPR

create or replace TYPE "CONCAT_EXPR" AS OBJECT (
  str VARCHAR2 (4000),
  del VARCHAR2 (4000),
  MAP MEMBER FUNCTION f_map RETURN VARCHAR2)
Type created
 
/
create or replace TYPE BODY concat_expr AS
  MAP MEMBER FUNCTION f_map
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN str;
  END f_map;
END;
Type body created
 
--concat_all_ot

create or replace TYPE "concat_all_ot"  AS OBJECT (
  str VARCHAR2 (4000),
  del VARCHAR2 (4000),

  STATIC FUNCTION odciaggregateinitialize (
  sctx IN OUT concat_all_ot)
  RETURN NUMBER,

  MEMBER FUNCTION odciaggregateiterate (
  SELF IN OUT concat_all_ot,
  ctx IN concat_expr)
  RETURN NUMBER,

  MEMBER FUNCTION odciaggregateterminate (
  SELF IN concat_all_ot,
  returnvalue OUT VARCHAR2,
  flags IN NUMBER)
  RETURN NUMBER,

  MEMBER FUNCTION odciaggregatemerge (
  SELF IN OUT concat_all_ot,
  ctx2 concat_all_ot)
  RETURN NUMBER)
Warning: Type created with compilation errors
 
/
create or replace TYPE BODY concat_all_ot
AS
  STATIC FUNCTION odciaggregateinitialize (
  sctx IN OUT concat_all_ot)
  RETURN NUMBER
  IS
  BEGIN
  sctx := concat_all_ot (NULL, NULL);
  RETURN odciconst.success;
  END;
  MEMBER FUNCTION odciaggregateiterate (
  SELF IN OUT concat_all_ot,
  ctx IN concat_expr)
  RETURN NUMBER
  IS
  BEGIN
  IF SELF.str IS NOT NULL THEN
    SELF.str := SELF.str || ctx.del;
  END IF;
  SELF.str := SELF.str || ctx.str;
  RETURN odciconst.success;
  END;

  MEMBER FUNCTION odciaggregateterminate (
  SELF IN concat_all_ot,
  returnvalue OUT VARCHAR2,
  flags IN NUMBER)
  RETURN NUMBER
  IS
  BEGIN
  returnvalue := SELF.str;
  RETURN odciconst.success;
  END;

  MEMBER FUNCTION odciaggregatemerge (
  SELF IN OUT concat_all_ot,
  ctx2 IN concat_all_ot)
  RETURN NUMBER
  IS
  BEGIN
  IF SELF.str IS NOT NULL THEN
    SELF.str := SELF.str || SELF.del;
  END IF;
  SELF.str := SELF.str || ctx2.str;
  RETURN odciconst.success;
  END;
END;
Warning: Type body created with compilation errors
 
create or replace FUNCTION concat_all (
  ctx IN concat_expr)
  RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
  AGGREGATE USING concat_all_ot;
Warning: Function created with compilation errors

Re: needed order by in concat_all() function [message #236193 is a reply to message #236192] Wed, 09 May 2007 04:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
WHAT error do you get? Do a "show error" when it returns with the message "Warning: Function created with compilation errors"
Re: needed order by in concat_all() function [message #236194 is a reply to message #236192] Wed, 09 May 2007 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add "show error" after EACH statement (I mean "/") to display the errors.
And post your screen with line numbers.

Regards
Michel

Re: needed order by in concat_all() function [message #236200 is a reply to message #236194] Wed, 09 May 2007 05:01 Go to previous messageGo to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member
Michel,

Please find attached file


Regards,
Prasad
  • Attachment: error.txt
    (Size: 3.40KB, Downloaded 144 times)
Re: needed order by in concat_all() function [message #236206 is a reply to message #236133] Wed, 09 May 2007 05:17 Go to previous messageGo to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member


Can you help me how to include a sort either at each step (odciaggregatemerge) or at the end (odciaggregateterminate).


Regards,
Prasad
Re: needed order by in concat_all() function [message #236211 is a reply to message #236194] Wed, 09 May 2007 05:55 Go to previous messageGo to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member

function created successfully. Only tell me how can we sort them. where we need to change the code


MEMBER FUNCTION odciaggregatemerge (
  SELF IN OUT concat_all_ot,
  ctx2 IN concat_all_ot)
  RETURN NUMBER
  IS
  BEGIN
  IF SELF.str IS NOT NULL THEN
    SELF.str := SELF.str || SELF.del;
  END IF;
  SELF.str := SELF.str || ctx2.str;
  RETURN odciconst.success;
  END;



MEMBER FUNCTION odciaggregateterminate (
  SELF IN concat_all_ot,
  returnvalue OUT VARCHAR2,
  flags IN NUMBER)
  RETURN NUMBER
  IS
  BEGIN
  returnvalue := SELF.str;
  RETURN odciconst.success;
  END;



Regards,
Prasad
Re: needed order by in concat_all() function [message #236213 is a reply to message #236200] Wed, 09 May 2007 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To fix errors, remove " around names.

Regards
Michel

Re: needed order by in concat_all() function [message #236214 is a reply to message #236211] Wed, 09 May 2007 06:16 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
There's no need to change the code because the function has a MAP function. Have a look at my test script below:
col yournames format a60

WITH yourtable 
AS
(
  SELECT 1 abo_rdo_code, 'United States'    country_name FROM dual UNION ALL
  SELECT 1 abo_rdo_code, 'Canada'           country_name FROM dual UNION ALL
  SELECT 2 abo_rdo_code, 'Fiji'             country_name FROM dual UNION ALL
  SELECT 2 abo_rdo_code, 'New Zealand'      country_name FROM dual UNION ALL
  SELECT 2 abo_rdo_code, 'Australia'        country_name FROM dual UNION ALL
  SELECT 2 abo_rdo_code, 'French Polynesia' country_name FROM dual UNION ALL
  SELECT 2 abo_rdo_code, 'Papua New Guinea' country_name FROM dual 
)
SELECT abo_rdo_code
     , MAX(yournames) yournames
FROM (
SELECT abo_rdo_code
     , CONCAT_ALL(CONCAT_EXPR(country_name,',')) OVER ( PARTITION BY abo_rdo_code ORDER BY country_name) yournames
FROM yourtable) group by abo_rdo_code
/

-- reverse sort:
WITH yourtable 
AS
(
  SELECT 1 abo_rdo_code, 'United States'    country_name FROM dual UNION ALL
  SELECT 1 abo_rdo_code, 'Canada'           country_name FROM dual UNION ALL
  SELECT 2 abo_rdo_code, 'Fiji'             country_name FROM dual UNION ALL
  SELECT 2 abo_rdo_code, 'New Zealand'      country_name FROM dual UNION ALL
  SELECT 2 abo_rdo_code, 'Australia'        country_name FROM dual UNION ALL
  SELECT 2 abo_rdo_code, 'French Polynesia' country_name FROM dual UNION ALL
  SELECT 2 abo_rdo_code, 'Papua New Guinea' country_name FROM dual 
)
SELECT abo_rdo_code
     , MAX(yournames) yournames
FROM (
SELECT abo_rdo_code
     , CONCAT_ALL(CONCAT_EXPR(country_name,',')) OVER ( PARTITION BY abo_rdo_code ORDER BY country_name DESC) yournames
FROM yourtable) group by abo_rdo_code
/

When I run it, I get this:
SQL> @orafaq

ABO_RDO_CODE YOURNAMES
------------ ------------------------------------------------------------
           1 Canada,United States
           2 Australia,Fiji,French Polynesia,New Zealand,Papua New Guinea


ABO_RDO_CODE YOURNAMES
------------ ------------------------------------------------------------
           1 United States,Canada
           2 Papua New Guinea,New Zealand,French Polynesia,Fiji,Australia


MHE
Re: needed order by in concat_all() function [message #236215 is a reply to message #236211] Wed, 09 May 2007 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It would be better to use the other way I suggested: fill an array at each step and sort it at the end to generate the final string.

Regards
Michel
Re: needed order by in concat_all() function [message #236216 is a reply to message #236213] Wed, 09 May 2007 06:17 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Wed, 09 May 2007 13:15
To fix errors, remove " around names.

Regards
Michel



Thanks for the pointer, Michel I'll modify the scripts.

MHE
Re: needed order by in concat_all() function [message #236218 is a reply to message #236214] Wed, 09 May 2007 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Didn't notice map function.

./fa/2115/0/

Regards
Michel

[Updated on: Wed, 09 May 2007 06:23]

Report message to a moderator

Re: needed order by in concat_all() function [message #236265 is a reply to message #236218] Wed, 09 May 2007 08:02 Go to previous messageGo to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member
Sorry I am not getting .... what about map function

please tell what changes to be needed in those two function
odciaggregatemerge or odciaggregateterminate

Regards,
Prasad
Re: needed order by in concat_all() function [message #236266 is a reply to message #236265] Wed, 09 May 2007 08:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
p.udas wrote on Wed, 09 May 2007 15:02
Sorry I am not getting .... what about map function

please tell what changes to be needed in those two function
odciaggregatemerge or odciaggregateterminate

Regards,
Prasad

NONE! Read my reply.

MHE

Again: THERE'S NO NEED TO CHANGE THE CODE.
Re: needed order by in concat_all() function [message #236280 is a reply to message #236266] Wed, 09 May 2007 08:31 Go to previous messageGo to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member
then how i can get sorted list in concat_all function
Re: needed order by in concat_all() function [message #236283 is a reply to message #236266] Wed, 09 May 2007 08:34 Go to previous messageGo to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member
ok ... using over (partition by ...)
but each time i have to write this code for every column which i want to sort in concat_all function

This is one solution. but there is no other solution apart from this
Re: needed order by in concat_all() function [message #236285 is a reply to message #236283] Wed, 09 May 2007 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The other solution is to modify the code.

Regards
Michel
Re: needed order by in concat_all() function [message #236291 is a reply to message #236285] Wed, 09 May 2007 08:46 Go to previous messageGo to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member
can anyone have modified code for sorted list. If have, please help in this issue .


Regards,
Prasad
Re: needed order by in concat_all() function [message #236292 is a reply to message #236291] Wed, 09 May 2007 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you try to create and share it?

Regards
Michel
Re: needed order by in concat_all() function [message #236303 is a reply to message #236291] Wed, 09 May 2007 09:18 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
p.udas wrote on Wed, 09 May 2007 15:46
can anyone have modified code for sorted list. If have, please help in this issue .


Regards,
Prasad

I can't help but thinking that this is trying to reinvent the wheel. One of the great things of aggregate functions is that you can sort and partition.

But feel free to have a go at it yourself.

MHE
Previous Topic: working with dates
Next Topic: cannot describe table via a dblink
Goto Forum:
  


Current Time: Tue Dec 06 12:30:29 CST 2016

Total time taken to generate the page: 0.09524 seconds