When using cast(collect(...)), how do I order the result?
I have a function called GetStringForTable, defined like this:
FUNCTION GetStringForTable(vTable in TVarCharTable, vDelimeter in varchar default ',') return VarChar2 is
aResult varchar2(32767);
i int;
begin
if vTable.count = 0 then
return '';
end if;
for i in 1 .. vTable.Count loop
if i > 1 then
aResult := aResult || vDelimeter;
end if;
aResult := aResult || vTable(i);
end loop;
return aResult;
end GetStringForTable;
And I use it like this:
select
name,
rep.GetStringForTable
((
Select
cast(collect(name) as TVarCharTable)
from
contacts
where
debtoraccount = dt.accountnumber
)
,', ' --Delimiter
) "Contacts"
from debtable dt
where name like '%Svein%';
The problem is that the result is not ordered. I get "Dave, Bob, Carol, Alice" when I would like "Alice, Bob, Carol, Dave". How do I order the result? If tried the following, but none works:
select
name,
rep.GetStringForTable
((
Select
cast(collect(name) as TVarCharTable)
from
contacts
where
debtoraccount = dt.accountnumber
order by name <= ORA-00907: missing right parenthesis
)
,', ' --Skilletegn
) "Contacts"
from debtable dt
where name like '%Svein%';
and
select
name,
rep.GetStringForTable
((
select * from
(
Select
cast(collect(name) as TVarCharTable)
from
contacts
where
debtoraccount = dt.accountnumber <= ORA-00904: string: invalid identifier
order by name
)
)
,', ' --Skilletegn
) "Contacts"
from debtable dt
where name like '%Svein%';
Ideally,I would like to do the ordering without changing the GetStringForTable-function.
Can anyone help?
one method would be to modify GetStringForTable so that the output comes ordered (you could have two functions: one ordered and the other not)
SQL> CREATE OR REPLACE TYPE TVarCharTable AS TABLE OF VARCHAR2(30);
2 /
Type created
SQL> CREATE OR REPLACE FUNCTION GetStringForTable(
2 vTable IN TVarCharTable,
3 vDelimeter IN VARCHAR DEFAULT ','
4 ) RETURN VARCHAR2 IS
5 aResult VARCHAR2(32767);
6 i INT := 1;
7 BEGIN
8 IF vTable.COUNT = 0 THEN
9 RETURN '';
10 END IF;
11 FOR cc IN (SELECT COLUMN_VALUE cv
12 FROM TABLE(CAST(vtable AS TVarCharTable))
13 ORDER BY COLUMN_VALUE) LOOP
14 IF i > 1 THEN
15 aResult := aResult || vDelimeter;
16 END IF;
17 aResult := aResult || cc.cv;
18 i := i+1;
19 END LOOP;
20 RETURN aResult;
21 END GetStringForTable;
22 /
Function created
SQL> SELECT GetStringForTable(TVarCharTable('B', 'A', 'D', 'C')) FROM dual;
GETSTRINGFORTABLE(TVARCHARTABL
---------------------------------------------------------------------
A,B,C,D
I've found a workaround, but unfortunately upon additional tests the ORDER is not guaranteed. It will depend upon the access path chosen. Still, it may work in your case :
SQL> SELECT dNAME,
2 GetStringForTable((SELECT CAST(COLLECT(eNAME) AS TVarCharTable)
3 FROM (SELECT *
4 FROM scott.emp
5 ORDER BY ename) e
6 /* ^^^^^^^^ */
7 WHERE e.deptno = dt.deptno),
8 ', ' --Delimiter
9 ) "Contacts"
10 FROM scott.dept dt;
DNAME Contacts
-------------- ----------------------------------------------------
ACCOUNTING CLARK, KING, MILLER
RESEARCH ADAMS, FORD, JONES, SCOTT, SMITH
SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
OPERATIONS
You can sort of force the ORDER with a trick to materialize the subquery, but this would prevent the optimizer from using most of the efficient paths, for example:
SQL> WITH employee AS (
2 SELECT *
3 FROM scott.emp
4 WHERE ROWNUM > 0 /* will materialize the subquery */
5 ORDER BY ename
6 )
7 SELECT dNAME,
8 GetStringForTable((SELECT CAST(COLLECT(eNAME) AS TVarCharTable)
9 FROM employee e
10 WHERE e.deptno = dt.deptno),
11 ', ' --Delimiter
12 ) "Contacts"
13 FROM scott.dept dt;
DNAME Contacts
-------------- -----------------------------------------------------
ACCOUNTING CLARK, KING, MILLER
RESEARCH ADAMS, FORD, JONES, SCOTT, SMITH
SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
OPERATIONS