Based on Oracle, LISTAGG orders data with each group specified in the ORDER BY clause and then concatenates the values of the measure column. It is a built in function that enables us to perform string aggregation. This Function is easy to learn and understand with examples. To demo the purpose of LISTAGG Function, we will be using emp table from baniya schema. Here’s how the data on emp table looks like.
SELECT deptno, ename from baniya.emp;
Output:
Example 1: Display all the last name of employees in a single line with comma separated list from deptno 20 and 30 only.
SQL Query:
SELECT LISTAGG(ename, ', ') WITHIN GROUP( ORDER BY empno) AS "Employee Last Names", MIN(hiredate) FROM baniya.emp WHERE deptno IN (30, 20);
Output:
Example 2: Display comma delimited last name of all the employees from each department and then order the last name by employee number.
SQL Query:
SELECT deptno, Listagg(ename, ',') within GROUP( ORDER BY empno) AS "Last Names" FROM baniya.emp GROUP BY deptno ORDER BY deptno;
Note: You can replace comma delimited with anything that you like, it could be semi column, pipe, empty space, back slash etc.
Recently, I was assigned to a ticket to work on a Function as to why it is not working as designed. The Function had a single query using LISTAGG Function and an Exception block to catch no data found exception. An email has to be send out to the development team when the code hits the exception block. The task is to identify and fix as why the functions is not sending out any email when process data that does not exist.
To explain the problem from the Function, I will use data from employee table and convert function into an anonymous block to simply the stuff. Our employee table does not have anyone with a last name TEST. The exception block should send email to developer team when processing employee that does not exist on employee table.When processing ename with TEST, we are NOT hitting NO_DATA_FOUND exception. Below is the PL/SQL and output for what I just explained.
SET serveroutput ON;DECLARE v_employee_count NUMBER; v_deptno NUMBER := 0; v_ename_list VARCHAR2(350); v_ename := ‘test’; BEGIN SELECT listagg(ename, ',') within GROUP( ORDER BY empno) AS "Last Names" INTO v_ename_list FROM baniya.emp WHERE ename = v_ename; dbms_output.Put_line('There is at least one employee in deptno ' ||v_deptno); dbms_output.put_line(‘employee exist’); EXCEPTION WHEN no_data_found THEN dbms_output.Put_line('There are NO employee in deptno ' ||v_deptno); --custom function to send email to developer team for research Send_exception_email(developer_14@abc.com, deptno, v_ename); dbms_output.put_line(‘email sent successfully!!’); END; /
Output:
PL/SQL procedure successfully completed.
There is at least one employee in deptno 0
Employee exist
The NO_DATA_FOUND exception does not work with LISTAGG when you don’t use it in a query that uses group by Function. The output from the PL/SQL block shows that the employee with ename TEST exist. Do you think employee with last name TEST exist? No, it does not. To research and troubleshoot this issue, I tried running just the SQL query to check the output.
SELECT Listagg(ename, ',') within GROUP( ORDER BY empno) AS "Last Names" -- INTO v_ename_list FROM baniya.emp WHERE ename LIKE ‘TEST’;
Output:
The above query displayed the result as NULL. Null is different than no data found. NO_DATA_FOUND does not work with the LISTAGG. You will need a IF ELSE to catch the Exception. I recommend you to review your LISTAGG Function and Exception block and ensure the Expectations are being handled properly.
In our example 2, we displayed the comma separated ename list for each dept. number. Let’s find out to check if this query handles no_data_found Exception. This query uses LISTAGG and GROUP BY clause to display data for each dept. We don’t have anyone in dept zero. Let’s use dept no zero to the PL/SQL block.
set serveroutput ON; DECLARE v_employee_count NUMBER; v_deptno NUMBER := 0; v_ename_list VARCHAR2(350); BEGIN SELECT deptno, Listagg(ename, ',') within GROUP( ORDER BY empno) AS "Last Names" INTO v_deptno, v_ename_list FROM baniya.emp GROUP BY deptno HAVING deptno = v_deptno ORDER BY deptno; dbms_output.Put_line('There is atleast one employee in deptno ' ||v_deptno); EXCEPTION WHEN no_data_found THEN dbms_output.Put_line('There are NO employee in deptno ' ||v_deptno); END; /
Output:
PL/SQL procedure successfully completed.
There are NO employee in deptno 0
The query throws an Exception for no_data_found and displays the appropriate message from dbms_output package. Why it worked? When running the query from body, it didn’t display NULL, instead it returned nothing meaning no data found.
SELECT deptno, Listagg(ename, ',') within GROUP( ORDER BY empno) AS "Last Names" FROM baniya.emp GROUP BY deptno HAVING deptno = 0 ORDER BY deptno;
Output:
No Data Found..
No Data Found..
LISTAGG is a great Function for string aggregation and a care must be given when using the EXCEPTION. Where possible, we should test all our exceptions.
Interested in working with me? I can be reached at pbaniya04[at]gmail.com for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.
Have a Database-ious Day!
Have a Database-ious Day!
How to using with out listagg in comma separated by names
ReplyDelete