Created On:  4 August 2010

Problem:

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

Resolution:

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)
as
begin
declare @curlength int, @length int
declare @rtnstring varchar(50)
select @rtnstring = '', @curlength = 0
while (@curlength < len(@indexnum)) -- loop until reach the last "." mark
begin
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
end
return @rtnstring
end

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