Incorrect (correct but i dont like it) aggregations in OBIEE 12.2.1.4

DENISKA80

I have two tables 

V1_DF_MAIN (ID_DECL PK, decl_brutto number,...)

V1_DF_FACT (id_fact PK, ID_DECL FK, fact_brutto number, fact_date date, ...)  

They have one to many relationship

Physical, Business and Pres layers are on pics

We have 2 measures. DECL_BRUTTO and FACT_BRUTTO.

1.1**************

if I dont set logical level for DECL_BRUTTO

I'll get following query which dublicates all decl_brutto values which have more than one fact values

WITH 

SAWITH0 AS (select sum(T311884.DECL_BRUTTO) as c1,

   T311813.fact_date as c2

from 

   V1_DF_FACT T311813,

   V1_DF_MAIN T311884 

where ( T311813.ID_DECL(+) = T311884.ID_DECL ) 

group by T311813.fact_date),

SAWITH1 AS (select sum(T311813.FACT_BRUTTO) as c1,

   T311813.fact_date as c2

from 

   V1_DF_FACT T311813

group by T311813.fact_date)

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1,

   D1.c2 as c2,

   D1.c3 as c3,

   D1.c4 as c4

from 

   (select 0 as c1,

        coalesce( D1.c2, D2.c2) as c2,

        D1.c1 as c3,

        D2.c1 as c4,

        ROW_NUMBER() OVER (PARTITION BY coalesce( D1.c2, D2.c2) ORDER BY coalesce( D1.c2, D2.c2) ASC) as c5

     from 

        SAWITH0 D1 full outer join SAWITH1 D2 On D1.c2 = D2.c2

   ) D1

where ( D1.c5 = 1 ) 

order by c2 ) D1 where rownum <= 250001

1.2**

If I set Logical level for DECL_BRUTTO

I will get following query which doesnt connect tables with each other using decl_id and show the same sum(value) of DECL_BRUTTO for all rows.

WITH 

SAWITH0 AS (select sum(T311813.FACT_BRUTTO) as c1,

   T311813.fact_date as c2

from 

   V1_DF_FACT T311813 

group by T311813.fact_date),

SAWITH1 AS (select sum(T311884.DECL_BRUTTO) as c1

from 

   V1_DF_MAIN T311884 )

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1,

   D1.c2 as c2,

   D1.c3 as c3,

   D1.c4 as c4

from 

   (select 0 as c1,

        D1.c2 as c2,

        D2.c1 as c3,

        D1.c1 as c4,

        ROW_NUMBER() OVER (PARTITION BY D1.c2 ORDER BY D1.c2 ASC) as c5

     from 

        SAWITH0 D1,

        SAWITH1 D2

   ) D1

where ( D1.c5 = 1 ) 

order by c2 ) D1 where rownum <= 250001

1.3******** But I'd like to get this query

with 

q1 as

(

select 

ID_DECL, 

decl_brutto 

from V1_DF_MAIN

),

q2 as 

(

select 

ID_DECL, 

fact_date, 

sum(fact_brutto) fact_brutto 

from V1_DF_FACT

group by ID_DECL, fact_date

)

select 

sum(q1.decl_brutto) brutto1, 

q2.fact_date, 

sum(q2.fact_brutto) fact_brutto 

from q1,q2

where q1.ID_DECL=q2.ID_DECL

group by q2.fact_date

If there is no any way to get it to work can I use this query somewhere in answers to get the correct results.

DDR feature can do this but it is closed for the end user.

Admin tab also closed