RE: SUM on CASE not working

Jayakumar Balasubramanya Thu, 25 Apr 2019 01:34:38 -0700

Hi,
               Just to further add on, we tried the option of 
enable-dynamic-column. When kylin.query.enable-dynamic-column=true the sum case 
expression works but gives wrong results when the where clause is on the 
aliased column, tried it in the sample cube of kylin.


Working case:

select sum(case when trans_id_alias in(11,12,13) then  price_alias else 0 end) 
as price_case_sum,part_dt_alias from (select price as price_alias ,trans_id as 
trans_id_alias,part_dt as part_dt_alias from kylin_sales where trans_id>10)
group by part_dt_alias
order by price_case_sum desc

[cid:[email protected]]

Not working case:

select sum(case when trans_id_alias in(11,12,13) then price_alias else 0 end) 
as price_case_sum,part_dt_alias from (select 
price_alias,trans_id_alias,part_dt_alias from (select price as price_alias 
,trans_id as trans_id_alias,part_dt as part_dt_alias from kylin_sales) where 
trans_id_alias>10)
group by part_dt_alias
order by price_case_sum desc


[cid:[email protected]]



Thanks,
JK


From: Jayakumar Balasubramanya
Sent: 25 April 2019 13:08
To: [email protected]
Subject: RE: SUM on CASE not working

Hi,
               I am using 2.6.1 version.

Thanks,
JK

From: ShaoFeng Shi <[email protected]<mailto:[email protected]>>
Sent: 25 April 2019 12:32
To: user <[email protected]<mailto:[email protected]>>
Subject: Re: SUM on CASE not working

What's your Kylin version? Kylin 2.4 and above should already support this.

Best regards,

Shaofeng Shi 史少锋
Apache Kylin PMC
Email: [email protected]<mailto:[email protected]>

Apache Kylin FAQ: https://kylin.apache.org/docs/gettingstarted/faq.html
Join Kylin user mail group: 
[email protected]<mailto:[email protected]>
Join Kylin dev mail group: 
[email protected]<mailto:[email protected]>




Jayakumar Balasubramanya 
<[email protected]<mailto:[email protected]>> 于2019年4月25日周四 
下午1:42写道：
Hi,
               I am facing a specific limitation when trying to roll-up a 
metric conditionally.  We are using case statement to either choose the metric 
or 0 and then doing a SUM on top of it. The condition is set on a dimension 
which is part of the cube. Getting an exception as stated below.

Query Not working:

Select Sum(Case when cyc_xxx_cyclesdelinquent>=2 THEN CYC_XXX_BALANCEADB ELSE 0 
end) from fct_profit_table_monthly
group by cyc_xxx_cyclesdelinquent

This is the exception for it:

No realization found for OLAPContext, CUBE_UNMATCHED_AGGREGATION[FunctionDesc 
[expression=SUM, parameter=CASE(>=($9, 2), CAST($4):DECIMAL(14, 4), 0), 
returnType=null]], rel#1153:OLAPTableScan.OLAP.[](table=[DEFAULT, 
FCT_PROFIT_TABLE_MONTHLY],ctx=,fields=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 
12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 
32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 
52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70]) 
while executing SQL: "Select Sum(Case when cyc_xxx_cyclesdelinquent>=2 THEN 
CYC_XXX_BALANCEADB ELSE 0 end) from fct_profit_table_monthly group by 
cyc_xxx_cyclesdelinquent LIMIT 50000"


This one works on Kylin:
SELECT CASE WHEN cyc_xxx_cyclesdelinquent>=2 THEN delinquent_bal ELSE 0 END 
FROM (Select SUM(Cyc_xxx_BalanceAdb) as delinquent_bal, 
cyc_xxx_cyclesdelinquent from fct_profit_table_monthly
group by cyc_xxx_cyclesdelinquent)


Regards,
JK

RE: SUM on CASE not working

Reply via email to