This query produces a SQL of the GL Accounts Tree. It can be used as is, but it often useful as a basis for other GL SQL's where the data should be displayed in tree format.

Notes

• The query only shows real accounts (no computed costs).

None. This is a selection query and no data is modified in the running of it.

DECLARE @ShowInactive bit;
--SET @ShowInActive = ;
SET @ShowInactive = 1;
DECLARE @Spacing INT;
SET @Spacing = 4;
SELECT 
   ROW_NUMBER() OVER(ORDER BY SortIndex) AS ROW,
   *
FROM
(
	SELECT
	  CASE WHEN IsLevel1=1 THEN SPACE(@Spacing * 0)
		ELSE CASE WHEN IsLevel2=1 THEN SPACE(@Spacing * 1)
		ELSE CASE WHEN IsLevel3=1 THEN SPACE(@Spacing * 2)
		ELSE CASE WHEN IsLevel4=1 THEN SPACE(@Spacing * 3)
		ELSE SPACE(@Spacing * 4)
		END END END END +
		COALESCE(Level5.AccountName, Level4.AccountName, Level3.AccountName, Level2.AccountName, Level1.AccountName) AS FormattedName,
	  COALESCE(Level5.AccountName, Level4.AccountName, Level3.AccountName, Level2.AccountName, Level1.AccountName) AS NodeName,
	  COALESCE(Level5.ExportAccountName, Level4.ExportAccountName, Level3.ExportAccountName, Level2.ExportAccountName, Level1.ExportAccountName, '') AS ExportName,
	  COALESCE(Level5.ExportAccountNumber, Level4.ExportAccountNumber, Level3.ExportAccountNumber, Level2.ExportAccountNumber, Level1.ExportAccountNumber, 0) AS ExportNumber,
	  COALESCE(Level5.ID, Level4.ID, Level3.ID, Level2.ID, Level1.ID) AS NodeID,
	  COALESCE(Level5.ClassTypeID, Level4.ClassTypeID, Level3.ClassTypeID, Level2.ClassTypeID, Level1.ClassTypeID) AS NodeClassTypeID,
	  COALESCE(Level5.IsActive, Level4.IsActive, Level3.IsActive, Level2.IsActive, Level1.IsActive) AS IsActive,
	  COALESCE(Level5.AccountGroupID, Level4.AccountGroupID, Level3.AccountGroupID, Level2.AccountGroupID, Level1.AccountGroupID) AS ParentID,
	  (IsLevel4 * 4 + IsLevel3 * 3 + IsLevel2 * 2 + IsLevel1 * 1) + COALESCE(Level5.ID - Level5.ID + 5, 0) AS Depth,
	  CASE WHEN COALESCE(Level5.ClassTypeID, Level4.ClassTypeID, Level3.ClassTypeID, Level2.ClassTypeID, Level1.ClassTypeID) = 8000 THEN 1 ELSE 0 END AS IsGroup,
	  Level1.AccountName AS Level1Name,
	  Level2.AccountName AS Level2Name,
	  Level3.AccountName AS Level3Name,
	  Level4.AccountName AS Level4Name,
	  Level5.AccountName AS Level5Name,
	  Level1.ID AS Level1ID,
	  Level2.ID AS Level2ID,
	  Level3.ID AS Level3ID,
	  Level4.ID AS Level4ID,
	  Level5.ID AS Level5ID,
      RIGHT( COALESCE( ('0000000'+ CAST( Round(Level1.SortIndex*1000,0) AS VARCHAR(10))+'/'), '') ,10) 
      + RIGHT( COALESCE( ('0000000'+ CAST( Round(Level2.SortIndex*1000,0) AS VARCHAR(10))+'/'), '') ,10) 
      + RIGHT( COALESCE( ('0000000'+ CAST( Round(Level3.SortIndex*1000,0) AS VARCHAR(10))+'/'), '') ,10) 
      + RIGHT( COALESCE( ('0000000'+ CAST( Round(Level4.SortIndex*1000,0) AS VARCHAR(10))+'/'), '') ,10) 
      + RIGHT( COALESCE( ('0000000'+ CAST( Round(Level5.SortIndex*1000,0) AS VARCHAR(10))+'/'), '') ,10) 
        AS SortIndex,
	  COALESCE(Level1.AccountName + N'\', '') +
		coalesce(Level2.AccountName + N'\', '') +
		coalesce(Level3.AccountName + N'\', '') +
		coalesce(Level4.AccountName + N'\', '') +
		coalesce(Level5.AccountName + N'\', '') as FormattedPath
	from GLAccount as Level1
	JOIN (SELECT 1 AS IsLevel1 UNION SELECT 0) Temp1 ON 1=1 -- create two rows for each of the above
	left join GLAccount as Level2 on Level1.ID = Level2.AccountGroupID and IsLevel1 = 0
	JOIN (SELECT 1 AS IsLevel2 UNION SELECT 0) Temp2 ON 1=1 -- create two rows for each of the above
	left join GLAccount as Level3 on Level2.ID = Level3.AccountGroupID and IsLevel1 = 0 and IsLevel2 = 0
	JOIN (SELECT 1 AS IsLevel3 UNION SELECT 0) Temp3 ON 1=1 -- create two rows for each of the above
	left join GLAccount as Level4 on Level3.ID = Level4.AccountGroupID and IsLevel1 = 0 and IsLevel2 = 0 and IsLevel3 = 0
	JOIN (SELECT 1 AS IsLevel4 UNION SELECT 0) Temp4 ON 1=1 -- create two rows for each of the above
	left join GLAccount as Level5 on Level4.ID = Level5.AccountGroupID and IsLevel1 = 0 and IsLevel2 = 0 and IsLevel3 = 0 and IsLevel4 = 0
	where (   (IsLevel1=1 and Level2.ID is NULL)
		   or (IsLevel2=1 and Level2.ID is not null and Level3.ID is NULL)
		   or (IsLevel3=1 and Level3.ID is not null and Level4.ID is NULL)
		   or (IsLevel4=1 and Level4.ID is not null and Level5.ID is NULL)
		   or (               Level5.ID is not NULL)
		  ) 
		  and (IsLevel1 + IsLevel2 + IsLevel3 + IsLevel4  0)
) GLTree
WHERE ((IsActive = 1) or (@ShowInactive = 1)) and (NodeClassTypeID in (8000, 8001))
ORDER By Row

• Entered : 8/25/2009, Revised 1/8/2010
• Version : Control 4.0+