Very useful SQL Scripts related queries, topics and discussions
User avatar
By admin
#356 Getting Accounting Dimensions defined in iScala Company Setup is easy thing.
The SQL script below is creating a VIEW called AccountingStringCC00 (just replace CC in the SQL script with your company code).

Code: Select allSET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[AccountingStringCC00]
AS

select
       [SegmentID],
       case when T1.SegmentID = 0 then 1
            when T1.SegmentID = 1 then 1 +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 0)

            when T1.SegmentID = 2 then 1 +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 0) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 1)

            when T1.SegmentID = 3 then 1 +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 0) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 1) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 2)

            when T1.SegmentID = 4 then 1 +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 0) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 1) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 2) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 3)

            when T1.SegmentID = 5 then 1 +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 0) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 1) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 2) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 3) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 4)


            when T1.SegmentID = 6 then 1 +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 0) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 1) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 2) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 3) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 4) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 5)

            when T1.SegmentID = 7 then 1 +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 0) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 1) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 2) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 3) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 4) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 5) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 6)

            when T1.SegmentID = 8 then 1 +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 0) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 1) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 2) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 3) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 4) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 5) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 6) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 7)

            when T1.SegmentID = 9 then 1 +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 0) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 1) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 2) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 3) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 4) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 5) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 6) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 7) +
                (select T2.Length from ScaCompanySegment T2 where T2.CompanyCode = 'CC' and T2.SegmentID = 8)


        end as [Pos],
       Length,
       [Name],
       [ShortName]

 from ScaCompanySegment T1 where CompanyCode = 'CC'
GO


When you execute the SELECT statement below, you will get all accounting dimensions within the CC company:

Code: Select allselect * from iScalaCompanyDatabase..AccountingStringCC00
Attachments
accounts.jpg
iScala Accounting Dimensions
accounts.jpg (40.86 KiB) Viewed 85345 times