4 Replies Latest reply on Oct 6, 2019 9:37 AM by Christian Berg

    Union all of two logical table source

    3281045

      Hi,

       

      I have three logical table source with following columns.

       

      LTS1: A1, B1, C1, D1, M1 (this has history data)

      LTS2: A1, B1, C1, M1 (this has history data)

      LTS3: A1, B1, M1 (this has history + incremental data)

       

      I want my query to pull complete data from LTS1 (or LTS2, depending upon the column selection in query) + only incremental data from LTS3. So, it should be like below.

       

      Query 1:

       

      Select A1, B1, C1, D1, sum(M1) from LTS1

      group by A1, B1, C1, D1

      UNION ALL

      Select A1, B1, NULL, NULL, sum(M1) from LTS3

      group by A1, B1;

       

      Query 2:

       

      Select A1, B1, C1, sum(M1) from LTS2

      group by A1, B1, C1

      UNION ALL

      Select A1, B1, NULL, sum(M1) from LTS3

      group by A1, B1

       

      I am able to generate union all for LTS1 & LTS3 using fragmentation but OBI server is not able to to choose between LTS1 & LTS2 as per the column selection. It is always selecting first LTS which is LTS1 in my case. So, only fist query is generated even though i select A1, B1 & M1 column only. Is there anyway to achieve the desired result.