Connect By Displayed as HTML

The Connect By displays the hierarchy of data. To generate the above report, we've indented the name by padding with three spaces for each level deep it is. Since we don't want to pad the 1st level, subtracting one puts it in the right place.

Select Empno,
Lpad(' '          -- Print a space
,3*(Level-1)  -- Pad with spaces for 3 times one less than the level
)  ||  Ename  -- Join spaces with the name to show hierarchy
As
EmpNm,
Mgr
From Emp
Start With Ename = 'KING'
Connect By Mgr = Prior Empno
Order Siblings By Ename;

If you wish to accomplish the same thing, but want to display it in a browser, Word or Excel, you'll need to make a few adjustments.

• For one thing, a space is virtually undetectable, so you'll want to increase the amount of space by quite a bit.
• A space has to be written as ' ', and since the ampersand is a variable to SQL, you need to add 'Set Escape On', and put a backslash ('\') in front of the ampersand.
• Setting 'Term Off' always helps since it just takes up time to watch HTML scroll by.
• The column that contains the escape character must be defined with 'Entmap Off', and must be long enough to contain all the 'nbsp;'s that you'll be adding without it wrapping to a second line. For instance, if the name is only formatted for 10 characters then the output would look like this:

EMPNO  EMPNM             MGR
------ ---------- ----------
7698    &nbs       7839
p;BLAKE

At least for Blake, we need a length of 17 (6+6+5).
• Use an extension of 'HTML' for a browser to automatically read the spool file; 'DOC' for Word; 'XLS' for Excel, etc.
• The length of the pad (in this case, 24) must be a multiple of six as there are six characters in ' ' and anything else will occasionally give you only parts of that code.
• The second expression in an LPAD is a default of space, but remember in the spooled file '   ' and '  are different.
  

Other than that, there's nothing to it! :o)

SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
Set Term Off
Set Escape On

Column Empnm Format A100 Entmap Off
Spool htmltest.html

Select Empno,
Lpad ('\ '     -- pad with spaces
,(Level-1)*24  -- the length of the pad is based on the level
,'\ '     -- fill in with this (default is space, but that's different)
)||Ename 
As 
Empnm,
Mgr
From Emp
Start With Ename = 'KING'
Connect By Mgr = Prior Empno
Order Siblings By Ename;

SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT OFF
Spool Off
Set Term On