Hello Folks
Need your brains on this particular piece.
There is a table with following fields - emp number, emp name, job number, super fund name and super fund percentage. This table holds information about employees and their related super funds and percentages.
create table test
(emp_number number,
emp_name varchar2(100),
job_num number,
s_fund varchar2(100),
s_perc number);
insert into test values (1, 'John', 21, 'AXA', 21);
insert into test values (1, 'John', 21, 'AAA', 20);
insert into test values (1, 'John', 21, 'ATC', 22);
I need to write 2 separate functions which should return the super name and their corresponding super percentages as comma separate values for an employee. The functions will have employee number as input parameter. Lets call the functions as FUNC_SUPER_NAME (EMP_NUMBER IN NUMBER) RETURN VARCHAR2
FUNC_SUPER_PERC (EMP_NUMBER IN NUMBER) RETURN VARCHAR2
For example, FUNC_SUPER_NAME when called for employee number 1, should return 'AXA,AAA,ATC';
and FUNC_SUPER_PERC when called for employee number 1, should return '21, 20, 22'.
Using LISTAGG, I wrote a SQL inside a function and its working all good. However, how can I ensure the order in which the two returns are produced by LISTAGG is consistent e.g. AXA corresponds to 21, AAA corresponds to 20 and ATC corresponds to 22. I do not want to return different percentage for values against their nominated super funds.
Thoughts anyone how to keep percentages and funds consistent while returning?