Wednesday, October 12, 2016

Hierarchical SQL with Tree Diagram output

The greatest value of a picture is when it forces us to notice what we never expected to see
                                                                                                              - John Wilder Tukey

Hierarchical SQL output is not easy to read in tabular format. I have seen developers trying to visualize the output in various ways. Provided you have a browser, it is not difficult to generate an output like the below using HTML and JavaScript.

Steps

1. Prepare Hierarchical SQL

I am using the classic EMP table in SCOTT schema.

SELECT e.ename node
,m.ename PARENT
,e.empno || ' ' || e.job tooltip
FROM scott.emp e
,scott.emp mWHERE e.mgr = m.empno(+)
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.job = 'PRESIDENT'

Please make sure that there are 3 columns in your SQL with aliases as below.

  1. Node :- Unique name for the node
  2. Parent :- Name of the parent Node
  3. Tooltip : Tooltip description to be displayed when mouse is moved over the node.

2. Compile the below procedure in database.



Step 3 : Execute the PLSQL procedure

Invoke the above PLSQL procedure with SQL in Step 1 as parameter. The output will be written to DBMS output. Please make sure that the output size is set accordingly.


Step 4 : Save the output as an htm file.

The output will be written with html tags with JavaScript. Save the output with an htm extension.

Step 5: Open the HTM file in web browser

This will display the hierarchical output as a tree diagram assuming the local machine has internet connection. Internet connection is required since the JavaScript tries to access the library on the Google website.

No comments: