Creating a string from values (procedure)

Question

Good day. Because oracle plsql developer from programming, but due to production needs, I got involved in this business, please help with writing a procedure in oracle plsql developer (I would be grateful for the help in creating the algorithm as well).

The essence is that - there is the first column (conditionally ID , in my context is not unique), and the second column, I will call it a "sign." The database is stored in this form.

 ID Признак 1 Красный 1 Темно-красный 2 Синий 2 Бирюзовый

It is necessary to write a procedure of this kind - all signs corresponding to the same ID are written in one cell. Those. So:

 ID Признак (применение процедуры) 1 Красный, (или ;) Темно-красный 2 Синий, Бирюзовый

I would be extremely grateful for the help! In a type of need I throw off a piece of select:

 (case when (row_number() over(partition by (select dd.name from metrology.document dd where dd.id in (select **max(d.document_id)** from metrology.eqtype_document d where d.EQUIPMENT_TYPE_ID = e.id)) order by e.name)) = 1 then (select dd.name from metrology.document dd where dd.id in (select **max(d.document_id)** from metrology.eqtype_document d where d.EQUIPMENT_TYPE_ID = e.id)) end) as doc_name_to_form

The characters ** mark a section of the code, because of which all fuss is produced. Max is included there purely for operability - this part of the select returns more than one line and causes an ora error, so I maxed it out by max, cutting it to one return result. But the fact is that in 70% of cases this section should return more than one value, which is why concatenation is necessary, and I do not know how to inject any code here.

I tried through the operator LISTAGG - but when using it, I get the error ORA-00904.
No, I don’t need to write the whole procedure, but I would be grateful for the description of the algorithm.
(case when (row_number () over (partition by (select listagg (dd.name, ',') from metrology.document dd where dd.id in (select (d.document_id) from metrology.eqtype_document d where d.EQUIPMENT_TYPE_ID = e.id)) order by e.name)) = 1 then (select listagg (dd.name, ',') from metrology.document dd where dd.id in (select (d.document_id) from metrology.eqtype_document d where d.EQUIPMENT_TYPE_ID = e.id)) end) as doc_name_to_form, (select string that needs to be replaced by the procedure) I found out why LISTAGG does not work for me - it is available from 11 oracle, I use 9.

Accepted Answer · 2017-06-26T07:35:43

As an option, if wm_conat or sys_xmlagg do not work, build recursive CTE:

 with Src(ID, Color, RN) as( select ID, Color, row_number() over(partition by ID order by NULL) RN from tab ), Tree(ID,color,RN) as( select ID, Color, RN from Src where RN=1 union all select T.ID,T.Color||','||S.Color,S.RN from Tree T, Src S where S.ID=T.ID and S.RN=T.RN+1 ) select * from Tree

Or this option (if you have sys_connect_by_path):

 select ID, substr(max(sys_connect_by_path(Color,',')),2) from ( select ID, Color, row_number() over(partition by ID order by NULL) RN from tab ) start with RN=1 connect by ID=prior ID and RN=prior RN+1 group by Id

Or make a function that receives a string by ID, which is probably more convenient for use in output forms:

 create or replace function get_colors(ID_ number) return varchar2 is result varchar2(4000); begin result:=NULL; for C in(select accname from v1users where vidid=ID_) loop if length(result)+length(C.accname) > 3996 then return substr(result||',...',2); end if; result:=result||','||C.accname; end loop; return substr(result,2); end;

and what approach through connect by better than wm_concat or xml ?
It seemed to me that connect by works rather slowly, and the result is the same.
@lDrakonl I don’t think it’s better, this is in case the vehicle doesn’t want to use wm_concat as undocumented and sys_xmlagg doesn’t work for it, as listagg did not go
@Mike, the solution with sys_connect_by_path was the closest — but in response, the error “result of concatenation was too great” got me out ”).
In the type of varchar2 can not remove the limit on the number of characters?)
In general, it seems to me that printing out a list of more than 2000 characters is a bit too much.
@ n0n4m3 In principle, if you want to insert this output in a certain and so complex query, in which these colors are not selected by individual lines now, then of course it can be easier to make a function that receives an ID as input and returns all colors to the output him
the truth will return then it is still varchar2 and it means there will be the same problems, but there it will be easier to control the length and in case of reaching a certain limit write '...' and stop

Answer 2 · 2017-06-26T06:48:53

You can get the desired result with the following query:

 select id, to_char(wm_concat("Признак")) as "Признак" from table group by id

It should be noted that the wm_concat function is undocumented and it is not always reasonable to use it. If wm_concat not suitable, you can use XML :

 select id, CAST(RTRIM(Sys_xmlagg(XMLELEMENT(col, id||', ')).extract('/ROWSET/COL/text()').getclobval(), ', ') AS VARCHAR2(4000)) as "Признак" FROM table group by id

col is your column. I could not use the Russian name of the column. In both cases, there may be problems with sorting values.

That is why I decided to try to solve the issue by the procedure)
I added my select to the first post in the topic with a description - if it doesn’t make it difficult - see how your code can be applied in my case.
How did you want to apply my queries, what result should I get for the whole big query?
If it is short again - this select works correctly, but returns more than one line, because of which I cut the result to one value in the area marked ** with 'max' (purely for temporary workability, not logical correctness).
Instead of this crutch, I wanted to come up with a solution that will concatenate the returned strings into one by a procedure, i.e.

Creating a string from values (procedure)

2 answers 2

More articles:

Creating a string from values ​​(procedure)

2 answers 2

More articles:

Creating a string from values (procedure)