Yes i have executed in 7.8 version too. Feeling the same.
Please find the query below
WITH cube_query as (
select
D_MOVEMENT,
D_GRP,
D_LINEMEMBER,
D_CUR,
D_ACCOUNT,
D_INTCOMP,
TIME,
DATASET,
O_TIME,
ACCTYPE,
PARENT_FLAG,
FORMULA_FLAG,
SUM(SIGNED_DATA) as SIGNED_DATA
from
( with D_ACCOUNT as (
select
distinct ID,
PARENT
from
SCD1_ACCOUNT
where
`PARENT` in ('A32000000','A31000000','A31901000','A31903700','A31903500','IC_INC')
), D_INTERCO as (
select
distinct `ID`,
`PARENT`
from
SCD1_INTCOMP
where
`PARENT` in('I0130', 'GI000', 'I0120', 'I_NONE')
and `ID` in('I0120', 'I0130', 'GI110', 'GI100', 'GI000', 'I_NONE', 'CONSO', 'G000', 'G100', 'E0060',
'E0270', 'E0590', 'E1150', 'E5550', 'G110', 'E0120', 'E0130', 'E0140', 'E0150', 'E0160', 'E0170', 'E0190',
)), D_TIME as (
select
distinct `ID`,
`PARENT`
from
SCD1_PERIOD
where
`PARENT` in('2021.TOTAL', '2021.Q1', '2021.P01', '2021.P02', '2021.P03', '2021.Q2', '2021.P04', '2021.P05','2021.P06'
, '2021.Q3', '2021.P07', '2021.P08', '2021.P09', '2021.Q4', '2021.P10', '2021.P11', '2021.P12')
)
select
fact.`D_MOVEMENT`,
fact.`D_GRP`,
fact.`D_LINEMEMBER`,
fact.`D_CUR`,
fact.`DATASET`,
fact.ENTITY,
D_ACCOUNT.`PARENT` as `D_ACCOUNT`,
D_INTERCO.`PARENT` as `D_INTCOMP`,
D_TIME.`PARENT` as `TIME`,
fact.`SIGNED_DATA`,
fact.`TIME` as O_TIME,
SCD2_ACCOUNT.ACCTYPE,
case
when D_ACCOUNT.ID <> D_ACCOUNT.PARENT then 'Y'
when D_INTERCO.ID <> D_INTERCO.PARENT then 'Y'
when D_TIME.ID <> D_TIME.PARENT then 'Y'
else 'N'
end as PARENT_FLAG,
case
when D_ACCOUNT.ID <> D_ACCOUNT.PARENT
and SCD2_ACCOUNT.`SUMMARYMETHODTYPE` = 'F' then 'Y'
when D_INTERCO.ID <> D_INTERCO.PARENT
and SCD2_ACCOUNT.`SUMMARYMETHODTYPE` = 'F' then 'Y'
when D_TIME.ID <> D_TIME.PARENT
and SCD2_ACCOUNT.`SUMMARYMETHODTYPE` = 'F' then 'Y'
else 'N'
end as FORMULA_FLAG
from
FACT_FINCONS fact
join D_ACCOUNT on
D_ACCOUNT.`ID` = fact.`D_ACCOUNT`
join D_INTERCO on
D_INTERCO.`ID` = fact.`D_INTCOMP`
join D_TIME on
D_TIME.`ID` = fact.`TIME`
LEFT join SCD2_ACCOUNT on
SCD2_ACCOUNT.`ID` = D_ACCOUNT.`PARENT`
join SCD2_INTCOMP on
fact.`D_INTCOMP` = SCD2_INTCOMP.`ID`
and (fact.`ENTITY` = SCD2_INTCOMP.`ENTITY`
or fact.`ENTITY` = 'E1930')
where
fact.`D_MOVEMENT` in('F99')
and fact.`D_GRP` in('S_NONE')
and fact.`D_LINEMEMBER` in('DUMMY')
and fact.`D_CUR` in('LC')
and fact.`DATASET` in('A1121')
and fact.`ENTITY` in('E1930', 'E2040','E0130')
and fact.`CATEGORY` in('Actual')
and fact.`CATEGORY` = 'Actual')f
group by
`D_MOVEMENT`,
`D_GRP`,
`D_LINEMEMBER`,
`D_CUR`,
`D_ACCOUNT`,
`D_INTCOMP`,
`TIME`,
`DATASET`,
`O_TIME`,
`ACCTYPE` ,
`PARENT_FLAG`,
`FORMULA_FLAG`
) ,
DRIVER_QUERY as
(
select
D_MOVEMENT ,
D_GRP,
D_LINEMEMBER,
D_CUR,
D_ACCOUNT,
D_INTCOMP,
TIME,
DATASET,
O_TIME,
ACCTYPE,
SUM(SIGNED_DATA) SIGNED_DATA
from
cube_query
where
(PARENT_FLAG = ‘N’
or FORMULA_FLAG = ‘N’
)
Group by D_MOVEMENT, D_GRP, D_LINEMEMBER, D_CUR, D_ACCOUNT, D_INTCOMP, TIME, DATASET
, O_TIME,ACCTYPE
UNION ALL
select
D_MOVEMENT,
D_GRP,
D_LINEMEMBER,
D_CUR,
‘A31903710’ as ACCOUNT,
`D_INTCOMP`,
`TIME`,
`DATASET`,
`O_TIME`,
`ACCTYPE`,
ifnull( (
select
SUM(B.SIGNED_DATA)
from
cube_query B
where
B.`D_ACCOUNT`= 'A31903700'
and B.`D_MOVEMENT` = A.`D_MOVEMENT`
and B.`D_GRP` = A.`D_GRP`
and B.`D_LINEMEMBER` = A.`D_LINEMEMBER`
and B.`D_CUR` = A.`D_CUR`
and B.`D_INTCOMP` = A.`D_INTCOMP`
and B.`TIME` = A.`TIME`
and B.`DATASET` = A.`DATASET`
and B.`O_TIME`=A.`O_TIME`
and B.`ACCTYPE`=A.`ACCTYPE`
and B.PARENT_FLAG = 'Y'
and B.FORMULA_FLAG = 'N')/ (
select
SUM(B.SIGNED_DATA)
from
cube_query B
where
B.`D_ACCOUNT`= 'A31903500'
and B.`D_MOVEMENT` = A.`D_MOVEMENT`
and B.`D_GRP` = A.`D_GRP`
and B.`D_LINEMEMBER` = A.`D_LINEMEMBER`
and B.`D_CUR` = A.`D_CUR`
and B.`D_INTCOMP` = A.`D_INTCOMP`
and B.`TIME` = A.`TIME`
and B.`DATASET` = A.`DATASET`
and B.`O_TIME`=A.`O_TIME`
and B.`ACCTYPE`=A.`ACCTYPE`
and B.PARENT_FLAG = 'Y'
and B.FORMULA_FLAG = 'N'),0) as SIGNED_DATA
from
cube_query A
where
PARENT_FLAG = 'Y'
and `D_ACCOUNT` in ('A31903500','A31903700')
group by
`D_MOVEMENT`,
`D_GRP`,
`D_LINEMEMBER`,
`D_CUR`,
`D_INTCOMP`,
`TIME`,
`DATASET`
,`O_TIME`,
`ACCTYPE`
)
– FOR ACCOUNT TYPE IS NULL CASE
select
B.D_MOVEMENT,
B.D_GRP,
B.D_LINEMEMBER,
B.D_CUR,
B.D_ACCOUNT,
B.D_INTCOMP,
B.DATASET,
B.TIME ,
SUM(B.SIGNED_DATA) as SIGNED_DATA
from
DRIVER_QUERY B
where
B.ACCTYPE is null
group by
B.D_MOVEMENT,
B.D_GRP,
B.D_LINEMEMBER,
B.D_CUR,
B.D_ACCOUNT,
B.D_INTCOMP,
B.DATASET,
B.TIME
union all
– FOR YTD LEAF INC&EXP
select
B.D_MOVEMENT,
B.D_GRP,
B.D_LINEMEMBER,
B.D_CUR,
B.D_ACCOUNT,
B.D_INTCOMP,
B.DATASET,
CONCAT(‘YTD:’, A.TIME) as TIME ,
case
when B.ACCTYPE = ‘INC’ then SUM(B.SIGNED_DATA) * -1
else SUM(B.SIGNED_DATA)
end as SIGNED_DATA
from
DRIVER_QUERY B
join DRIVER_QUERY A on
( convert(right(B.O_TIME, 2), UNSIGNED ) <= convert(right(A.O_TIME, 2), UNSIGNED )
and B.D_MOVEMENT= A.D_MOVEMENT
and B.D_GRP= A.D_GRP
and B.D_LINEMEMBER= A.D_LINEMEMBER
and B.D_CUR= A.D_CUR
and B.D_ACCOUNT= A.D_ACCOUNT
and B.D_INTCOMP= A.D_INTCOMP
and B.DATASET= A.DATASET
and left(A.O_TIME, 4)= left(B.O_TIME, 4) )
where
B.ACCTYPE in (‘INC’, ‘EXP’)
and A.ACCTYPE in (‘INC’, ‘EXP’)
and B.TIME = B.O_TIME
and A.TIME = A.O_TIME
group by
B.D_MOVEMENT,
B.D_GRP,
B.D_LINEMEMBER,
B.D_CUR,
B.D_ACCOUNT,
B.D_INTCOMP,
B.DATASET,
A.TIME
union all
– FOR YTD PARENT INC&EXP
select
B.D_MOVEMENT,
B.D_GRP,
B.D_LINEMEMBER,
B.D_CUR,
B.D_ACCOUNT,
B.D_INTCOMP,
B.DATASET,
CONCAT(‘YTD:’, B.TIME) as TIME ,
case
when B.ACCTYPE = ‘INC’ then SUM(B.SIGNED_DATA) * -1
else SUM(B.SIGNED_DATA)
end as SIGNED_DATA
from
DRIVER_QUERY B
where
B.ACCTYPE in (‘INC’, ‘EXP’)
and B.TIME <> B.O_TIME
group by
B.D_MOVEMENT,
B.D_GRP,
B.D_LINEMEMBER,
B.D_CUR,
B.D_ACCOUNT,
B.D_INTCOMP,
B.DATASET,
B.TIME
union all
– FOR YTD LEAF ASSET & LEQ
select
B.D_MOVEMENT,
B.D_GRP,
B.D_LINEMEMBER,
B.D_CUR,
B.D_ACCOUNT,
B.D_INTCOMP,
B.DATASET,
CONCAT(‘YTD:’, B.TIME) as TIME ,
case
when B.ACCTYPE = ‘LEQ’ then B.SIGNED_DATA * -1
else B.SIGNED_DATA
end as SIGNED_DATA
from
DRIVER_QUERY B
where
B.ACCTYPE in (‘AST’, ‘LEQ’)
and B.TIME = B.O_TIME
union all
– FOR YTD PARENT ASSET & LEQ
select
distinct B.D_MOVEMENT,
B.D_GRP,
B.D_LINEMEMBER,
B.D_CUR,
B.D_ACCOUNT,
B.D_INTCOMP,
B.DATASET,
CONCAT(‘YTD:’, B.TIME ) TIME ,
last_value(case when B.ACCTYPE = ‘LEQ’ then B.SIGNED_DATA * -1 else B.SIGNED_DATA end )
over (partition by B.D_MOVEMENT,B.D_GRP, B.D_LINEMEMBER,B.D_CUR,B.D_ACCOUNT,B.D_INTCOMP,B.DATASET, B.TIME
order by B.D_MOVEMENT,B.D_GRP,B.D_LINEMEMBER,B.D_CUR,B.D_ACCOUNT,B.D_INTCOMP,B.DATASET,B.TIME,
cast(right(B.O_TIME, 2) as SIGNED ) range between unbounded preceding and unbounded following ) as SIGNED_DATA
from
DRIVER_QUERY B
where
B.ACCTYPE in (‘AST’, ‘LEQ’)
and B.TIME <> B.O_TIME
union all
– FOR PER LEAF INC&EXP
select
B.D_MOVEMENT,
B.D_GRP,
B.D_LINEMEMBER,
B.D_CUR,
B.D_ACCOUNT,
B.D_INTCOMP,
B.DATASET,
CONCAT(‘PER:’, B.TIME) as TIME ,
case
when B.ACCTYPE = ‘INC’ then B.SIGNED_DATA * -1
else B.SIGNED_DATA
end as SIGNED_DATA
from
DRIVER_QUERY B
where
B.ACCTYPE in (‘INC’, ‘EXP’)
and B.TIME = B.O_TIME
union all
– FOR PER PARENT INC&EXP
select
B.D_MOVEMENT,
B.D_GRP,
B.D_LINEMEMBER,
B.D_CUR,
B.D_ACCOUNT,
B.D_INTCOMP,
B.DATASET,
CONCAT(‘PER:’, B.TIME) as TIME ,
case
when B.ACCTYPE = ‘INC’ then SUM(B.SIGNED_DATA) * -1
else SUM(B.SIGNED_DATA)
end SIGNED_DATA
from
DRIVER_QUERY B
where
B.ACCTYPE in (‘INC’, ‘EXP’)
and B.TIME <> B.O_TIME
group by
B.D_MOVEMENT,
B.D_GRP,
B.D_LINEMEMBER,
B.D_CUR,
B.D_ACCOUNT,
B.D_INTCOMP,
B.DATASET,
B.TIME
union all
– FOR PER LEAF ASSET & LEQ
select
B.D_MOVEMENT,
B.D_GRP,
B.D_LINEMEMBER,
B.D_CUR,
B.D_ACCOUNT,
B.D_INTCOMP,
B.DATASET,
CONCAT(‘PER:’, B.TIME) as TIME ,
case
when B.ACCTYPE = ‘LEQ’ then B.SIGNED_DATA * -1
else B.SIGNED_DATA
end as SIGNED_DATA
from
DRIVER_QUERY B
where
B.ACCTYPE in (‘AST’, ‘LEQ’)
and B.TIME = B.O_TIME;
