0 Replies Latest reply on Sep 20, 2020 11:51 PM by Gerdo Veenink

    Datamodel structuregroup

    Gerdo Veenink Active Member

      For correct representation of data in our datawarehouse we want to extract data from our database to the datawarehouse.

       

      But now I'm some sort of stuck because can't find the cause for the case below.

       

      We have a season structure which looks like

       

      Seasons

      level 1 - 2020

      level 2 - S21

      level 3 - S21LAM

      level 2 - X21

      level 3 - X21BAL

       

      etc

      During the creation of this group we've made a typo. So level 1 was called 2019 (Name and Identifier). We've changed the Name and Identifier to 2020 but now I'm not able to compose the correct query to retrieve the correct version.

      The query

      SELECT sgr.Identifier as "Code", sgl.Name as "Name", sgd.ParentIdentifier as "Parent"

        FROM [HPM_MAIN].[dbo].[StructureGroupDetail] sgd

        ,    [HPM_MAIN].[dbo].[StructureGroupRevision] sgr

        ,  [HPM_MAIN].[dbo].[StructureGroupLang] sgl

      ,               [HPM_MAIN].[dbo].[Language]                             lang

      where  sgr.StructureID =   (select max(StructureID) 

                                          FROM [HPM_MAIN].[dbo].[StructureRevision]

                                          where Identifier =  'Seasons')

      and  sgr.RevisionID =   (select max(sgrr.RevisionID) 

                                          FROM [HPM_MAIN].[dbo].[StructureGroupRevision] sgrr

                                          where sgrr.Identifier =  sgr.Identifier

                                                                    )

      and sgd.StructureGroupRevisionID = sgr.ID

      and sgl.StructureGroupRevisionID = sgr.ID

      and sgl.LanguageID  = lang.ID

      and        lang.Alpha3Code = 'eng'

      and        lang.Locale = 'en'

       

      I think we are missing something in the bold section. Because it returns 2019 in stead of 2020. But based on the data I can't find the distinctive part of the data.

       

      Hope someone can help.

       

      Regards,

      Gerdo