Created On:  4 August 2010


Is it possible to sort data by hierarchy when using Caliber Datamart with SQL Server?


The hierarchy field is included in table name "RequirementInfo".

Please use the following procedure in the SQL Server query dialog:

create function dbo.SortNumber(@indexnum varchar(50))
returns varchar(50)
declare @curlength int, @length int
declare @rtnstring varchar(50)
select @rtnstring = '', @curlength = 0
while (@curlength < len(@indexnum)) -- loop until reach the last "." mark
set @length = charindex('.', @indexnum, @curlength + 1)
if @length = 0
set @length = len(@indexnum) + 1
-- pading with leading zero
set @rtnstring = @rtnstring + right('0000' + substring(@indexnum, @curlength + 1, @length - @curlength - 1), 4) + '.'
set @curlength = @length
return @rtnstring

Then run the SELECT SQL query below. Note that the SELECT section of the query can be adjusted to suit your own required fields but Type, Hierarchy and Sort reference must always remain:

SELECT Distinct Hierarchy, Type, Requirement_id, Requirement_Name,dbo.SortNumber(Hierarchy) SortedIndex 
FROM DBO.requirementinfo
ORDER BY Type, SortedIndex

The output result will now be displayed in hierarchial order.

Incident #2463236