View: dbo.GL_Account_Segment_Code_Sort_View | |||
View definition | |||
--drop view dbo.GL_Account_Segment_Code_Sort_View create view dbo.GL_Account_Segment_Code_Sort_View as select m.gl_account_segment_KEY , c.gl_account_segment_code_KEY , case -- A numeric segment code. It's assigned a sort string with padding (of zeroes) to the right and left of the -- segment code such that the decimal point (if any) will line up with all other numeric account number -- decimal points. when patindex(N'%[^0-9.]%',c.code) = 0 and patindex(N'%.%.%',c.code) = 0 then replicate(N'0', m.max_left__char_count - c.left__char_count) + left(c.code, c.left__char_count) + N'.' + right(c.code, c.right__char_count) + replicate(N'.', m.max_right__char_count - c.right__char_count) -- An alpha-numeric account number. Its sort string is simply itself. else c.code end as code_sortable from ( select gl_account_segment_KEY , max_left__char_count = max(case when charindex(N'.', code) > 0 then charindex(N'.', code) - 1 else len(code) end) , max_right__char_count = max(case when charindex(N'.', code) > 0 then len(code) - charindex(N'.', code) else 0 end) from dbo.GL_Account_Segment_Code group by gl_account_segment_KEY -- The mask metrics should be able to be derived less expensively from the GL_Account_Segment table but this -- won't work until we have constraints in place that force the segment codes to match the mask's pattern. For -- now, this code has to remain commented out and the metrics will be gathered directly from existing codes. ) as m inner join ( select gl_account_segment_KEY , gl_account_segment_code_KEY , code , left__char_count = case when charindex(N'.', code) > 0 then charindex(N'.', code) - 1 else len(code) end , right__char_count = case when charindex(N'.', code) > 0 then len(code) - charindex(N'.', code) else 0 end from dbo.GL_Account_Segment_Code ) as c on m.gl_account_segment_KEY = c.gl_account_segment_KEY | |||