plsql - LISTAGG IN PL/SQL -
help please find mistake in creating function return 1 row list.
select listagg(' ' || v_receiving_list.document_number || chr(13)) within group (order v_receiving_list.document_number) svc.claim_detail, tbc.v_receiving_list claim_detail.id_claim = 334455 , v_receiving_list.id_receiving_list = claim_detail.id_receiving_list
the code return correct result.
but next dont return same.
declare scodes varchar2 (4000); begin select (listagg(' ' || receiving_list.document_number || chr(13)) within group (order receiving_list.document_number)) scodes svc.claim_detail, tbc.v_receiving_list claim_detail.id_claim = 334455 , receiving_list.id_receiving_list = claim_detail.id_receiving_list; dbms_output.put_line(scodes); end;
sql> select * v_receiving_list ; document_number id_receiving_list --------------- -------------------- 1 1 2 b sql> select * claim_detail ; id_claim id_receiving_list ---------- -------------------- 123 123 124 b sql> declare 2 scodes varchar2 (4000); 3 begin 4 select (listagg(' '|| v_receiving_list.document_number || chr(13)) 5 within group (order v_receiving_list.document_number)) 6 7 scodes 8 claim_detail, v_receiving_list 9 claim_detail.id_claim = 123 10 , v_receiving_list.id_receiving_list = claim_detail.id_receiving_list; 11 12 13 dbms_output.put_line('the output '||scodes); 14 end; 15 / 1e output 1 pl/sql procedure completed.