slowview.sql

create view dbo.FuckingSlow
as
  select 'derivatives'                    as section,
         Rtrim(Accounts.indicator)
         + Rtrim(Departments.indicator)
         + Rtrim(Products.indicator)    as 'Account',
         Rtrim(Accounts.indicator)
         + Rtrim(Departments.indicator)
         + Rtrim(Products.indicator) + '0000'
         + Rtrim(Derivatives.indicator) as 'FullAccount',
         Accounts.indicator             as AccType,2
         Departments.indicator          as Department,
         Products.indicator             as Product,
         Derivatives.indicator          as Derivative,
         Accounts.description           as AccTypeName,
         Departments.description        as DepartmentName,
         Products.description           as ProductName,
         Derivatives.description        as DerivativeName,
         case
           when Isnull(Products.iscredit, Accounts.iscredit) = 1 then 1
           else 0
         end                              as IsCredit,
         Products.indicatorid           as 'ProductIndicatorId',
         1                                as 'HasDerivative',
         Derivatives.description        as 'Description',
         Rtrim(Accounts.indicator)
         + Rtrim(Departments.indicator)
         + Rtrim(Products.indicator) + '0000'
         + Rtrim(Derivatives.indicator) + ' '
         + Accounts.description + ' - '
         + Departments.description + ' - '
         + Products.description + ' - '
         + Derivatives.description      as 'FullDescription',
         case ( Cast(Accounts.Active as int)
                + Cast(Derivatives.Active as int)
                + Cast(Products.Active as int)
                + Cast(Departments.Active as int) )
           when 4 then 1
           else 0
         end                              as Active
  from   indicators Accounts
         join indicators Departments
           on Departments.indicatorlevel = 1
              and Departments.acctype = Accounts.indicator
         join indicators Products
           on Products.indicatorlevel = 2
              and Products.parentindicatorid = Departments.indicatorid
         join indicators Derivatives
           on Derivatives.indicatorlevel = 5
              and Derivatives.parentindicatorid = Products.indicatorid
  where  Accounts.indicatorlevel = 0
  union
  select 'accounttypes'                            as section,
         Rtrim(Accounts.indicator) + '000'       as 'Account',
         Rtrim(Accounts.indicator) + '000000000' as 'FullAccount',
         Accounts.indicator                      as AccType,
         ''                                        as Department,
         '00'                                      as Product,
         null                                      as Derivative,
         Accounts.description                    as AccTypeName,
         ''                                        as DepartmentName,
         ''                                        as ProductName,
         null                                      as DerivativeName,
         Accounts.iscredit,
         '00'                                      as 'ProductIndicatorId',
         1                                         as 'HasDerivative',
         Accounts.description                    as 'Description',
         Rtrim(Accounts.indicator) + '000' + ' '
         + Accounts.description                  as 'FullDescription',
         Accounts.Active
  from   indicators Accounts
  where  Accounts.indicatorlevel = 0
  union
  /*Accumulated department*/
  select 'departments'                                 as section,
         Rtrim(Accounts.indicator)
         + Rtrim(Departments.indicator) + '00'       as 'Account',
         Rtrim(Accounts.indicator)
         + Rtrim(Departments.indicator) + '00000000' as 'FullAccount',
         Accounts.indicator                          as AccType,
         Departments.indicator                       as Department,
         '00'                                          as Product,
         null                                          as Derivative,
         Accounts.description                        as AccTypeName,
         Departments.description                     as DepartmentName,
         ''                                            as ProductName,
         null                                          as DerivativeName,
         Accounts.iscredit,
         '00'                                          as 'ProductIndicatorId',
         1                                             as 'HasDerivative',
         Departments.description                     as 'Description',
         Rtrim(Accounts.indicator)
         + Rtrim(Departments.indicator) + '00' + ' '
         + Accounts.description + ' - '
         + Departments.description                   as 'FullDescription',
         case ( Cast(Accounts.Active as int)
                + Cast(Departments.Active as int) )
           when 2 then 1
           else 0
         end                                           as Active
  from   indicators Accounts
         join indicators Departments
           on Departments.indicatorlevel = 1
              and Departments.acctype = Accounts.indicator
  where  Accounts.indicatorlevel = 0
  union
  select 'products'                               as section,
         Rtrim(Accounts.indicator)
         + Rtrim(Departments.indicator)
         + Rtrim(Products.indicator)            as 'Account',
         Rtrim(Accounts.indicator)
         + Rtrim(Departments.indicator)
         + Rtrim(Products.indicator) + '000000' as 'FullAccount',
  from   indicators Accounts
         join indicators Departments
           on Departments.indicatorlevel = 1
              and Departments.acctype = Accounts.indicator
         join indicators Products
           on Products.indicatorlevel = 2
              and Products.parentindicatorid = Departments.indicatorid
  where  Accounts.indicatorlevel = 0