1

Topic: To link contents

It is assured that such it is possible to make, but I will not think as:
There is an initial label:
The nomenclature, the registration Account
1300201006,91
1300201007,23
1300201007,91
1300201009,91
1300201010,23
1300201010,91
1300201013,23
1300201017,23
1300201017,91
1300201017,92
1300201020,91
1300201020,94
It is necessary to group records as follows:
The nomenclature, the registration Account
1300201006,91
1300201007,23/91
1300201009,91
1300201010,23/91
1300201013,23
1300201017,23/91/92
1300201020,91/94
Advise as to implement it in PL/SQL?
In advance I thank!

2

Re: To link contents

mbm wrote:

in [color=red] PL / SQL? [/color]

RTFM LISTAGG (FAQ)

3

Re: To link contents

4

Re: To link contents

to the Students, wishing the help
Show that you tried to make, begin with the test data:

with t (ID, ACCOUNT) as (
select 1300201006,91 from dual union all
select 1300201007,23 from dual union all
select 1300201007,91 from dual union all
select 1300201009,91 from dual union all
select 1300201010,23 from dual union all
select 1300201010,91 from dual union all
select 1300201013,23 from dual union all
select 1300201017,23 from dual union all
select 1300201017,91 from dual union all
select 1300201017,92 from dual union all
select 1300201020,91 from dual union all
select 1300201020,94 from dual
)
select id, account from t;

5

Re: To link contents

Elic wrote:

RTFM LISTAGG (FAQ)

Thanks, it seem that is necessary, but...
Function LISTAGG can be used in Oracle upcoming versions / PLSQL: Oracle 12c, Oracle 11g Release 2
And there is any analog under Oracle8i?

6

Re: To link contents

mbm wrote:

it is passed...
Thanks, it seem that is necessary, but...
Function LISTAGG can be used in Oracle upcoming versions / PLSQL: Oracle 12c, Oracle 11g Release 2
And there is any analog under Oracle8i?

It is possible to request at , for old versions they produce on demand.

7

Re: To link contents

mbm wrote:

Oracle8i?

I sympathize.
RTFM THE TOP No14

8

Re: To link contents

Vadim Lejnin wrote:

Show that you tried to make, begin with the test data:

SQL> ed
Wrote file afiedt.buf
1 declare
2 v_s varchar2 (4000);
3 v_id number;
4 begin
5 for r in (
6 with t (ID, ACCOUNT) as (
7 select 1300201006,91 from dual union all
8 select 1300201007,23 from dual union all
9 select 1300201007,91 from dual union all
10 select 1300201009,91 from dual union all
11 select 1300201010,23 from dual union all
12 select 1300201010,91 from dual union all
13 select 1300201013,23 from dual union all
14 select 1300201017,23 from dual union all
15 select 1300201017,91 from dual union all
16 select 1300201017,92 from dual union all
17 select 1300201020,91 from dual union all
18 select 1300201020,94 from dual
19)
20 select id, account from t order by id) loop
21 if v_id=r.id then
22 v_s: = v_s || ' / ' || r. ACCOUNT;
23 else
24 if v_id is not null then
25 dbms_output.put_line (v_id || ' ' || v_s);
26 end if;
27 v_s: = r. ACCOUNT;
28 v_id: = r.id;
29 end if;
30 end loop;
31 if v_id is not null then
32 dbms_output.put_line (v_id || ' ' || v_s);
33 end if;
34* end;
SQL> /
1300201006 91
1300201007 23/91
1300201009 91
1300201010 23/91
1300201013 23
1300201017 23/91/92
1300201020 91/94
PL/SQL procedure successfully completed.
SQL>

....
stax

9

Re: To link contents

mbm wrote:

And there is any analog under Oracle8i?

ROW_NUMBER + hierarchical query + to remove a thick dust layer from my packet 302640 .
SY.

10

Re: To link contents

to Stax
THANKS!
I did not have no "push"!
Altered: records are selected from one table (TMP_DEBET_AMORT) and aggregated in another (TMP_DEBET_AMORT_GR):

declare
v_acc varchar2 (100);
v_nomen number;
begin
delete from TMP_DEBET_AMORT_GR;
for r in (select nomen_code, amort_debet from TMP_DEBET_AMORT group by nomen_code, amort_debet order by nomen_code)
loop
if v_nomen=r.nomen_code
then v_acc: = v_acc || ' / ' || r.amort_debet;
else
if v_nomen is not null
then insert into TMP_DEBET_AMORT_GR (nomen_code, amort_debet) values (v_nomen, v_acc);
end if;
v_acc: = r.amort_debet;
v_nomen: = r.nomen_code;
end if;
end loop;
if v_nomen is not null
then insert into TMP_DEBET_AMORT_GR (nomen_code, amort_debet) values (v_nomen, v_acc);
end if;
commit;
end;

11

Re: To link contents

mbm wrote:

it is passed...
Thanks, it seem that is necessary, but...
Function LISTAGG can be used in Oracle upcoming versions / PLSQL: Oracle 12c, Oracle 11g Release 2
And there is any analog under Oracle8i?

And that 8 more  whom that, cool

12

Re: To link contents

wm_concat.

13

Re: To link contents

Jebrail wrote:

wm_concat.

and you even can show its usage on the version 8i?
And anything, what it appeared strongly later (at all in the nine if that) and and remained not documented?

14

Re: To link contents

  - Eh wrote:

at all in the nine, if that

And here , please. Since I in 9.2 used it precisely.
Documented it with 10 how much I remember.

15

Re: To link contents

env;
Not earlier, than Jebrail shows that it can be used in 9i

16

Re: To link contents

env wrote:

how much I remember.

"there are only two which (storages) I trust. One of them is I, and the second - not you" () wink
Itself you can prove, what you was exact"in the version 9i, instead of in 10g, for example?

17

Re: To link contents

env wrote:

And here , please. Since I in 9.2 used it precisely.

SQL> select object_type, count (*) from all_objects where owner = ' WMSYS ' group by object_type;
OBJECT_TYPE COUNT (*)
------------------ ----------
INDEX 33
PROCEDURE 1
SEQUENCE 9
TABLE 23
TYPE 5
VIEW 58
6 rows selected.
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

18

Re: To link contents

Kind  - Eh;
And there is any analog live sql for 8i, 9i etc.?

19

Re: To link contents

  - Eh wrote:

Itself you can prove, what you was exact"in the version 9i, instead of in 10g, for example?

I on 11g skipped from 9.2.0.4. Strongly it was necessary to face 10th later.

20

Re: To link contents

Jebrail wrote:

wm_concat.

10 does not remain sad

Connected to Oracle8i Enterprise Edition Release 8.1.7.3.0
select wm_concat (dummy) from dual
ORA-00904: invalid column name
Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
select wm_concat (dummy) from dual
ORA-00904: "WM_CONCAT": invalid identifier
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
select wm_concat (dummy) from dual
WM_CONCAT (DUMMY)
--------------------------------------------------------------------------------
X
Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
select wm_concat (dummy) from dual
ORA-00904: "WM_CONCAT": invalid identifier

That is
In 8-ke WM_CONCAT was not
Generally, to advise to use not documentary function which was only in two major versions, thus also changed (result with varchar2 on clob) - not so well

21

Re: To link contents

WM_CONCAT;
Thanks. Means either storage brings, or one kind person "" 9.2.0.4 functional from 10 for the needs.
Vitaly, wm_concat in 10g enters in  or is rolled from circuit scripts wmsys?

22

Re: To link contents

env wrote:

WM_CONCAT;
Thanks. Means either storage brings, or one kind person "" 9.2.0.4 functional from 10 for the needs.

SQL> CREATE OR REPLACE
type WM_CONCAT_IMPL wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
d
270 160
bg9hz+fBwa888VEZGViHFs/oOP0wg433f64df3QCWE7VehmhTFcUe3y+JrsniV3cSuvmnE3g
Y93dtR+cCsU1N+UQDGbtzhCf2HIdr8lPzfgF2bmCTvmGlHQbTAjTftNrDq3p093ncwb32OyX
3ZFDTeH2jpjm3uWYyT8kZBfJIYxRwgLfRApoW32cpy0eRnvDBt2XfTAMXKCSNnqSoTiGA83W
6deKW+rWyBu9L/EPyFkmQZeBncNsiNDF8fa1Sm6vdQiEanlCQnaPJ11a0na8hK6psDSaey+x
fdMupCwSvg6gMrSV4QCguhOCqW2AmxRVMqpXJootPpTBxBFZc7hORGbriUI=
/
Type created.
SQL> CREATE OR REPLACE function wm_concat wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
58 96
+CuW1MAfZPVR6NOnXKVmXY2o9pswg8eZgcfLCNL+XhZy8K7/cgzcVrPnfMPnx3TAM7h0ZSXD
j57Asr2ym9ZtFldFmFu+RdpAU8VGORKSvtVG+DmAOR4C+NTa+Pit2kDbEpI5zhZUgqameSkE
DQ ==
/
Function created.
SQL> select wm_concat (object_id) from sys.user_objects
/
WM_CONCAT (OBJECT_ID)
--------------------------------------------------------------------------------
108674,108672,53144,53147,53150,53151,53162
1 row selected.

.....
stax

23

Re: To link contents

Current do not tell that you in 9-ku pushed a packet with format WRAP from 10

24

Re: To link contents

Vyacheslav Ljubomudrov;
On the contrary with 10-> above
ps
  did not try
.....
stax

25

Re: To link contents

Then explain that you wanted to show in the previous post