Consolidated Segment Code Column for MIP Transaction Tables

originally posted 2013-10-2

1	originally posted 2013-10-2

The chart of accounts (COA) is different for each organization. The COA code segments are expressed as individual columns in the table structure, making it difficult to query based on code values without resorting to dynamic SQL.

A possible solution is, for each table that contains the COA codes, to create a single calculated-persisted column that consolidates the multi-column values into a single delimited column. In this case, the values are consolidated with a caret (^) character. This example determines the formula for the new calculated-persisted field by examining the COA definition in the tblSegmentInfo table, then it creates the new [CodeSequence] column for each target table. The schema is checked to see if the column and its associated index already exist, and if so, it drops them before (re-)creating them. The tables involved only need to be the ones that the integrating application cares about, in this particular case, they are…

tblBLTrans – budget detail table
tblENTrans – encumbrance detail table
tblDLTrans – expense detail table
tblGLBLBalance – transaction summary table
tblDistCodeDetail – distribution codes, which are named sequences of codes without the GL (0) segment

declare @sequence nvarchar(2000)
set @sequence = '';

--Determine the number of COA segments
declare @total int
select @total = count(*) from tblSegmentInfo

declare @index int
set @index=0;

--Build the formula string that will be used in the calculated column
while(@index<@total)
begin
  set @sequence = @sequence + 'ltrim(rtrim(isnull([sCodeIDf_' + convert(nvarchar(10),@index) + '] ,''''))) + ''^'''
  if(@index+1 < @total) set @sequence = @sequence + ' + ';
  set @index=@index+1
end

--Build the sql template string to create the new [CodeSequence] calculated-persisted field and its index. Drop both first if the column exists.
--The {0} is a placeholder for the table name 
declare @sqlTemplate nvarchar(max);
set @sqlTemplate = N'
if exists 
(
  select * 
  from sys.columns 
  where [name] = N''CodeSequence'' 
  and [is_computed] = 1
  and [object_id] = Object_ID(N''{0}'')
)
begin
  drop index [dbo].[{0}].[IX_{0}_CodeSequence];
  alter table [dbo].[{0}] drop column [CodeSequence];
end
alter table [dbo].[{0}] add [CodeSequence] as ' + @sequence + ' PERSISTED;
create nonclustered index [IX_{0}_CodeSequence] on [dbo].[{0}] ([CodeSequence] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];';

declare @sql nvarchar(max);

--Replace the {0} in the sql template string with the tablename and execute the script for each table of interest

--tblDistCodeDetail - no gl code
set @sql = replace(@sqlTemplate,'{0}','tblDistCodeDetail');
set @sql = replace(@sql,'ltrim(rtrim(isnull([sCodeIDf_0] ,''''))) + ''^'' + ','');
exec(@sql);

--tblEnTrans - all codes
set @sql = replace(@sqlTemplate,'{0}','tblEnTrans');
exec(@sql);

--tblDlTrans - all codes
set @sql = replace(@sqlTemplate,'{0}','tblDlTrans');
exec(@sql);

--tblBlTrans - all codes
set @sql = replace(@sqlTemplate,'{0}','tblBlTrans');
exec(@sql);

--tblGLBLBalance - all codes
set @sql = replace(@sqlTemplate,'{0}','tblGLBLBalance');
exec(@sql);

declare @sequence nvarchar(2000)

set @sequence = '';

--Determine the number of COA segments

declare @total int

select @total = count(*) from tblSegmentInfo

declare @index int

set @index=0;

--Build the formula string that will be used in the calculated column

while(@index<@total)

begin

set @sequence = @sequence + 'ltrim(rtrim(isnull([sCodeIDf_' + convert(nvarchar(10),@index) + '] ,''''))) + ''^'''

if(@index+1 < @total) set @sequence = @sequence + ' + ';

set @index=@index+1

end

--Build the sql template string to create the new [CodeSequence] calculated-persisted field and its index. Drop both first if the column exists.

--The {0} is a placeholder for the table name

declare @sqlTemplate nvarchar(max);

set @sqlTemplate = N'

if exists

(

select *

from sys.columns

where [name] = N''CodeSequence''

and [is_computed] = 1

and [object_id] = Object_ID(N''{0}'')

)

begin

drop index [dbo].[{0}].[IX_{0}_CodeSequence];

alter table [dbo].[{0}] drop column [CodeSequence];

end

alter table [dbo].[{0}] add [CodeSequence] as ' + @sequence + ' PERSISTED;

create nonclustered index [IX_{0}_CodeSequence] on [dbo].[{0}] ([CodeSequence] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];';

declare @sql nvarchar(max);

--Replace the {0} in the sql template string with the tablename and execute the script for each table of interest

--tblDistCodeDetail - no gl code

set @sql = replace(@sqlTemplate,'{0}','tblDistCodeDetail');

set @sql = replace(@sql,'ltrim(rtrim(isnull([sCodeIDf_0] ,''''))) + ''^'' + ','');

exec(@sql);

--tblEnTrans - all codes

set @sql = replace(@sqlTemplate,'{0}','tblEnTrans');

exec(@sql);

--tblDlTrans - all codes

set @sql = replace(@sqlTemplate,'{0}','tblDlTrans');

exec(@sql);

--tblBlTrans - all codes

set @sql = replace(@sqlTemplate,'{0}','tblBlTrans');

exec(@sql);

--tblGLBLBalance - all codes

set @sql = replace(@sqlTemplate,'{0}','tblGLBLBalance');

exec(@sql);