|
create database ttlmaxqa3_ecash_dw1;
use ttlmaxqa3_ecash_dw1;
CREATE TABLE `tmp_tb_ldh_gpawnv3` (
`id` int(10) unsigned DEFAULT NULL,
`effective_due_date` date DEFAULT NULL,
`loan_model_id` int(10) unsigned DEFAULT NULL,
`status_id` int(10) unsigned DEFAULT NULL,
`bus_date_created` date DEFAULT NULL,
`group_id` int(10) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8
insert into tmp_tb_ldh_gpawnv3 values
(26964124,'2014-03-19',10,6,'2014-02-17',NULL),(26964125,'2014-03-19',13,6,'2014-02-17',NULL),(26964126,'2014-03-19',42,6,'2014-02-17',NULL),(26964129,'2014-04-28',42,6,'2014-02-27',NULL),(26964133,'2014-04-16',42,6,'2014-03-17',NULL),(26964135,'2014-04-26',42,6,'2014-03-27',NULL),(26964160,'2014-06-29',42,6,'2014-05-30',NULL),(26964176,'2014-08-08',42,6,'2014-07-09',NULL),(26964419,'2015-03-09',42,6,'2015-02-07',NULL),(26965407,'2015-12-12',42,3,'2015-11-12',NULL),(26966910,'2017-03-26',42,6,'2017-02-24',NULL)
ERROR 1815 (HY000): Internal error: 'days_late' cannot be found in tuple.
select status_id,
|
IFNULL((
|
CASE WHEN status_id = 13 AND SUM(IFNULL(status_id,0)) > 0 THEN
|
|
CASE WHEN IFNULL(IF(days_late<0,0,days_late),0) THEN
|
ABS(ABS(ROUND(SUM(IFNULL(id,0)),4)) - SUM(loan_model_id))
|
ELSE
|
0
|
END
|
END
|
|
),0) AS accrued_bal
|
from
|
(
|
select id,status_id, loan_model_id, DATEDIFF(@asOfDate,tmp_tb_ldh_gpawnv3.effective_due_date) AS days_late from tmp_tb_ldh_gpawnv3 limit 5
|
) a group by status_id
|
select status_id,
|
IFNULL((
|
CASE WHEN status_id = 13 AND SUM(IFNULL(status_id,0)) > 0 AND IFNULL(IF(days_late<0,0,days_late),0) THEN
|
ABS(ABS(ROUND(SUM(IFNULL(id,0)),4)) - SUM(loan_model_id))
|
ELSE
|
0
|
END
|
),0) AS accrued_bal
|
from
|
(
|
select id,status_id, loan_model_id, DATEDIFF(@asOfDate,tmp_tb_ldh_gpawnv3.effective_due_date) AS days_late from tmp_tb_ldh_gpawnv3 limit 5
|
) a group by status_id;
|
Note: The work around is to flatten the case logic to a single teir as seen in the working example
|