Genesys Infomart Reporting - Get ConnID from GIM database

Recently, I answered question about ‘Conn Id in Infomart?’ in SGGU(www.sggu.com) forum and referred to the link ‘Link ConnId In Infomart’.  However, requester want to retrieve Conn ID directly from Genesys Infomart. As Conn Id field is stored in decimal format in Infomart database (GIM), it was not useful as T-Server log uses hexadecimal format.

In C#, you can simply call Int64.ToString(“X”) to convert integer into hexadecimal format but there were no inbuilt functions within SQL Server. I wrote SQL Server function to convert decimal to hex as below..

CREATE FUNCTION ConvertToBase
(
    @value AS BIGINT
) RETURNS VARCHAR(MAX) 
AS 
BEGIN
    -- some variables
    DECLARE @characters CHAR(36),
            @result VARCHAR(MAX),
        @base int;
 
    -- the encoding string 
    SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',
           @result = '',
           @base = 16;
 
    -- Convert it into hex
    WHILE @value > 0
        SELECT @result = SUBSTRING(@characters, @value % @base + 1, 1) + @result,
               @value = @value / @base;
 
    -- Prefix 0 
    set @result = N'0'+ @result;
    
    -- return our results
    RETURN @result;
 
END

Above function is Patrick Caldwell blog post about converting decimals to base numbers and big thanks to him.

Update 13-Aug-2014:

SQL query to get Conn ID from Infomart database is as below

select top 1
	interaction_id,
	dbo.converttobase(media_server_ixn_id)
from
	interaction_segment_fact
order by
	interaction_id desc