You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@impala.apache.org by Si...@truecorp.co.th on 2016/09/23 03:44:03 UTC
Re: COUNT(DISTINCT col) returns wrong results: Impala ODBC: Error
retry
Best Regards,
Siksit Santirojanakul (Eak)
IT - CIS Customer Delivery / Campaign
True Info Tech Co., Ltd.
Tel. 02-699-7428
From: Siksit Santirojanakul/True Corp
To: user@impala.incubator.apache.org
Date: 21/09/2016 14:54
Subject: Fw: COUNT(DISTINCT col) returns wrong results: Impala
ODBC: Error
Dear All,
Have problem from impala Impala 2.2.2 , odbc driver for Impala
2.05.28.1008. Error msg "Exception: DBD[Cloudera][ImpalaODBC] (110) Error
while excuting a query in Impala: [HY000]: illegalState: Illegal reference
to no-materialized slot: tid=0 sid=5 State: S1000"
My Sql Query:
with a1 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
, COUNT(DISTINCT L.AUDIENCEKEY) AS TOTAL_MSISDN
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0
END) AS TOPUP_COUNT_20
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0
END) AS TOPUP_COUNT_30
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0
END) AS TOPUP_COUNT_50
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0
END) AS TOPUP_COUNT_100
, SUM(CASE WHEN CDR_TYPE = 1 THEN 1 ELSE 0 END) AS TOPUP_COUNT
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN
TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_20
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN
TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_30
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN
TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_50
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN
TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_100
, SUM(CASE WHEN CDR_TYPE = 1 THEN TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT
, SUM(CASE WHEN CDR_TYPE = 2 THEN 1 ELSE 0 END) AS FIRSTCALL_COUNT
, COUNT(*) AS LUCKYCODE_COUNT
, SUM(CASE WHEN trim(PROVISION_ID) <> '' THEN BONUS_AMOUNT ELSE 0 END) AS
REDEEM_AMOUNT
, SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0 END) AS REDEEM_TRAN_20
, SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0 END) AS REDEEM_TRAN_30
, SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0 END) AS REDEEM_TRAN_50
, SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0 END) AS REDEEM_TRAN_100
, SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 THEN 1 ELSE 0
END) AS REDEEM_TRAN
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0
END) AS TOPUP_TRAN_20
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0
END) AS TOPUP_TRAN_30
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0
END) AS TOPUP_TRAN_50
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0
END) AS TOPUP_TRAN_100
, SUM(CASE WHEN CDR_TYPE = 1 THEN 1 ELSE 0 END) AS TOPUP_TRAN
, SUM(CASE WHEN trim(PROVISION_ID) <> '' THEN 1 ELSE 0 END) AS
REDEEM_COUNT
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN
BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_20
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN
BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_30
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN
BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_50
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN
BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_100
, SUM(CASE WHEN CDR_TYPE = 1 THEN BONUS_AMOUNT ELSE 0 END) AS
TOTAL_BONUS_AMOUNT
, SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
F.PROVISIONPARAM4 = '20'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_20
, SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
F.PROVISIONPARAM4 = '30'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_30
, SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
F.PROVISIONPARAM4 = '50'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_50
, SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
F.PROVISIONPARAM4 = '100'THEN BONUS_AMOUNT ELSE 0 END) AS
REDEEM_AMOUNT_100
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a2 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 THEN L.AUDIENCEKEY ELSE NULL END)
AS TOPUP_MSISDN
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a3 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' THEN L.AUDIENCEKEY ELSE
'' END) AS REDEEM_MSISDN
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a4 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '20' THEN L.AUDIENCEKEY ELSE null END) AS
REDEEM_MSISDN_20
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a5 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '30' THEN L.AUDIENCEKEY ELSE null END) AS
REDEEM_MSISDN_30
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a6 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '50' THEN L.AUDIENCEKEY ELSE null END) AS
REDEEM_MSISDN_50
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a7 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '100' THEN L.AUDIENCEKEY ELSE null END) AS
REDEEM_MSISDN_100
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a8 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN
L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_20
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a9 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN
L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_30
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a10 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN
L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_50
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a11 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN
L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_100
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
SELECT
COALESCE(a1.LUCKY_DATE, a12.LUCKY_DATE) as LUCKY_DATE,
COALESCE(a12.TOTAL_REGISTED, last_value(a12.TOTAL_REGISTED) OVER (order by
COALESCE(a1.LUCKY_DATE, a12.LUCKY_DATE) ASC)) AS TOTAL_REGISTED,
COALESCE(a12.TODAY_REGISTED, 0) AS TODAY_REGISTED,
COALESCE(a1.TOTAL_MSISDN, 0) AS TOTAL_MSISDN,
COALESCE(a1.TOPUP_AMOUNT_20, 0) AS
TOPUP_AMOUNT_20,COALESCE(a1.TOPUP_AMOUNT_30, 0) AS TOPUP_AMOUNT_30,
COALESCE(a1.TOPUP_AMOUNT_50, 0) AS TOPUP_AMOUNT_50,
COALESCE(a1.TOPUP_AMOUNT_100, 0) AS TOPUP_AMOUNT_100,
COALESCE(a1.TOPUP_AMOUNT, 0) AS TOPUP_AMOUNT,
COALESCE(a1.FIRSTCALL_COUNT, 0) AS FIRSTCALL_COUNT,
COALESCE(a1.LUCKYCODE_COUNT, 0) AS LUCKYCODE_COUNT,
COALESCE(a1.BONUS_AMOUNT_20, 0) AS
BONUS_AMOUNT_20,COALESCE(a1.BONUS_AMOUNT_30, 0) AS
BONUS_AMOUNT_30,COALESCE(a1.BONUS_AMOUNT_50, 0) AS BONUS_AMOUNT_50,
COALESCE(a1.BONUS_AMOUNT_100, 0) AS BONUS_AMOUNT_100,
COALESCE(a1.TOTAL_BONUS_AMOUNT, 0) AS TOTAL_BONUS_AMOUNT,
COALESCE(a1.REDEEM_AMOUNT_20, 0) AS
REDEEM_AMOUNT_20,COALESCE(a1.REDEEM_AMOUNT_30, 0) AS REDEEM_AMOUNT_30,
COALESCE(a1.REDEEM_AMOUNT_50, 0) AS REDEEM_AMOUNT_50,
COALESCE(a1.REDEEM_AMOUNT_100, 0) AS REDEEM_AMOUNT_100,
COALESCE(a1.REDEEM_AMOUNT, 0) AS REDEEM_AMOUNT,
COALESCE(a4.REDEEM_MSISDN_20, 0) AS
REDEEM_MSISDN_20,COALESCE(a5.REDEEM_MSISDN_30, 0) AS
REDEEM_MSISDN_30,COALESCE(a6.REDEEM_MSISDN_50, 0) AS
REDEEM_MSISDN_50,COALESCE(a7.REDEEM_MSISDN_100, 0) AS REDEEM_MSISDN_100,
COALESCE(a3.REDEEM_MSISDN, 0) AS REDEEM_MSISDN,
COALESCE(a1.REDEEM_TRAN_20, 0) AS
REDEEM_TRAN_20,COALESCE(a1.REDEEM_TRAN_30, 0) AS
REDEEM_TRAN_30,COALESCE(a1.REDEEM_TRAN_50, 0) AS
REDEEM_TRAN_50,COALESCE(a1.REDEEM_TRAN_100, 0) AS REDEEM_TRAN_100,
COALESCE(a1.REDEEM_TRAN, 0) AS REDEEM_TRAN,
COALESCE(a1.TOPUP_COUNT_20, 0) AS
TOPUP_COUNT_20,COALESCE(a1.TOPUP_COUNT_30, 0) AS
TOPUP_COUNT_30,COALESCE(a1.TOPUP_COUNT_50, 0) AS
TOPUP_COUNT_50,COALESCE(a1.TOPUP_COUNT_100, 0) AS TOPUP_COUNT_100,
COALESCE(a1.TOPUP_COUNT, 0) AS TOPUP_COUNT,
COALESCE(a8.TOPUP_MSISDN_20, 0) AS
TOPUP_MSISDN_20,COALESCE(a9.TOPUP_MSISDN_30, 0) AS
TOPUP_MSISDN_30,COALESCE(a10.TOPUP_MSISDN_50, 0) AS
TOPUP_MSISDN_50,COALESCE(a11.TOPUP_MSISDN_100, 0) AS TOPUP_MSISDN_100,
COALESCE(a2.TOPUP_MSISDN, 0) AS TOPUP_MSISDN,
COALESCE(a1.TOPUP_TRAN_20, 0) AS TOPUP_TRAN_20, COALESCE(a1.TOPUP_TRAN_30,
0) AS TOPUP_TRAN_30, COALESCE(a1.TOPUP_TRAN_50, 0) AS TOPUP_TRAN_50,
COALESCE(a1.TOPUP_TRAN_100, 0) AS TOPUP_TRAN_100, COALESCE(a1.TOPUP_TRAN ,
0) AS TOPUP_TRAN,
COALESCE(a13.TODAY_REGISTED_SUCC, 0) TODAY_REGISTED_SUCC,
COALESCE(a14.REDEEM_SUCC, 0 ) AS REDEEM_SUCC,
COALESCE(a14.REDEEM_FAIL, 0 ) AS REDEEM_FAIL,
COALESCE(a15.sum_input, 0 ) aS SUM_INPUT,
COALESCE(a15.sum_output, 0 ) aS sum_output
from a1
FULL OUTER JOIN unica.v_Today_Reg a12 on a1.LUCKY_DATE = a12.LUCKY_DATE
FUll OUTER JOIN unica.v_Today_Reg_Succ a13 on a1.LUCKY_DATE=
a13.LUCKY_DATE
FUll OUTER JOIN unica.V_Redeem a14 on a1.LUCKY_DATE= a14.LUCKY_DATE
FULL JOIN unica.V_Eligible a15 on a1.LUCKY_DATE = a15.PROCESS_DATE
FULL JOIN a2 on a1.LUCKY_DATE = a2.LUCKY_DATE
FULL JOIN a3 on a1.LUCKY_DATE = a3.LUCKY_DATE
FULL JOIN a4 on a1.LUCKY_DATE = a4.LUCKY_DATE
FULL JOIN a5 on a1.LUCKY_DATE = a5.LUCKY_DATE
FULL JOIN a6 on a1.LUCKY_DATE = a6.LUCKY_DATE
FULL JOIN a7 on a1.LUCKY_DATE = a7.LUCKY_DATE
FULL JOIN a8 on a1.LUCKY_DATE = a8.LUCKY_DATE
FULL JOIN a9 on a1.LUCKY_DATE = a9.LUCKY_DATE
FULL JOIN a10 on a1.LUCKY_DATE = a10.LUCKY_DATE
FULL JOIN a11 on a1.LUCKY_DATE = a11.LUCKY_DATE
where cast(a1.LUCKY_DATE as timestamp) = cast('2016-09-09' as timestamp)
Best Regards,
Siksit Santirojanakul (Eak)
IT - CIS Customer Delivery / Campaign
True Info Tech Co., Ltd.
Tel. 02-699-7428
Important
Confidentiality: This Information is intended for the above-named person and may contain confidential and/or legally privileged material. Any opinions expressed in this information are not necessarily those of the company. If it has come to you in error you must take no action based on it, nor must you copy or show it to anyone; please delete/destroy and inform the sender immediately.
Monitoring/Viruses
True Corporation and subsidiaries reserves the right to monitor all incoming and outgoing emails via True Corporation and subsidiaries's systems. Although we have security program to monitor and eliminate virus, we also advise that in keeping with good computing practice the recipient should ensure they are actually virus free.
Re: COUNT(DISTINCT col) returns wrong results: Impala ODBC: Error
Posted by Alex Behm <al...@cloudera.com>.
Certainly a new version of Impala (not ODBC). Sorry this is causing you
trouble.
On Sun, Sep 25, 2016 at 8:05 PM, <Si...@truecorp.co.th> wrote:
> Dear All,
>
>
> for the later versions impala or new version odbc for impala ? If new
> version for impala have a big problem for deploy on my produciton!!!!
>
>
> Best Regards,
> Siksit Santirojanakul (Eak)
> IT - CIS Customer Delivery / Campaign
> True Info Tech Co., Ltd.
> Tel. 02-699-7428
>
>
>
>
> From: Tim Armstrong <ta...@cloudera.com>
> To: user@impala.incubator.apache.org
> Cc: user@impala.apache.org, user-subscribe@impala.
> incubator.apache.org
> Date: 24/09/2016 06:46
> Subject: Re: COUNT(DISTINCT col) returns wrong results: Impala
> ODBC: Error
> ------------------------------
>
>
>
> It's probably one of the multiple bugs along this line that have been
> fixed in later versions of Impala. E.g.
> *https://issues.cloudera.org/browse/IMPALA-2216?filter=11799*
> <https://issues.cloudera.org/browse/IMPALA-2216?filter=11799>
>
> On Thu, Sep 22, 2016 at 8:44 PM, <*Siksit_San@truecorp.co.th*
> <Si...@truecorp.co.th>> wrote:
> retry
>
>
> Best Regards,
> Siksit Santirojanakul (Eak)
> IT - CIS Customer Delivery / Campaign
> True Info Tech Co., Ltd.
> Tel. 02-699-7428
>
>
>
>
> From: Siksit Santirojanakul/True Corp
> To: *user@impala.incubator.apache.org*
> <us...@impala.incubator.apache.org>
> Date: 21/09/2016 14:54
> Subject: Fw: COUNT(DISTINCT col) returns wrong results: Impala
> ODBC: Error
> ------------------------------
>
>
> Dear All,
>
> Have problem from impala Impala 2.2.2 , odbc driver for Impala
> 2.05.28.1008. Error msg *"Exception: DBD[Cloudera][ImpalaODBC] (110)
> Error while excuting a query in Impala: [HY000]: illegalState: Illegal
> reference to no-materialized slot: tid=0 sid=5 State: S1000"*
>
> * My Sql Query: *
>
> with a1 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> , COUNT(DISTINCT L.AUDIENCEKEY) AS TOTAL_MSISDN
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0
> END) AS TOPUP_COUNT_20
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0
> END) AS TOPUP_COUNT_30
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0
> END) AS TOPUP_COUNT_50
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0
> END) AS TOPUP_COUNT_100
> , SUM(CASE WHEN CDR_TYPE = 1 THEN 1 ELSE 0 END) AS TOPUP_COUNT
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN
> TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_20
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN
> TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_30
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN
> TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_50
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN
> TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_100
> , SUM(CASE WHEN CDR_TYPE = 1 THEN TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT
> , SUM(CASE WHEN CDR_TYPE = 2 THEN 1 ELSE 0 END) AS FIRSTCALL_COUNT
> , COUNT(*) AS LUCKYCODE_COUNT
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' THEN BONUS_AMOUNT ELSE 0 END) AS
> REDEEM_AMOUNT
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0 END) AS REDEEM_TRAN_20
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0 END) AS REDEEM_TRAN_30
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0 END) AS REDEEM_TRAN_50
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0 END) AS REDEEM_TRAN_100
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 THEN 1 ELSE 0
> END) AS REDEEM_TRAN
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0
> END) AS TOPUP_TRAN_20
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0
> END) AS TOPUP_TRAN_30
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0
> END) AS TOPUP_TRAN_50
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0
> END) AS TOPUP_TRAN_100
> , SUM(CASE WHEN CDR_TYPE = 1 THEN 1 ELSE 0 END) AS TOPUP_TRAN
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' THEN 1 ELSE 0 END) AS
> REDEEM_COUNT
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN
> BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_20
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN
> BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_30
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN
> BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_50
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN
> BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_100
> , SUM(CASE WHEN CDR_TYPE = 1 THEN BONUS_AMOUNT ELSE 0 END) AS
> TOTAL_BONUS_AMOUNT
> , SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
> F.PROVISIONPARAM4 = '20'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_20
> , SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
> F.PROVISIONPARAM4 = '30'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_30
> , SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
> F.PROVISIONPARAM4 = '50'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_50
> , SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
> F.PROVISIONPARAM4 = '100'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_100
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a2 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 THEN L.AUDIENCEKEY ELSE NULL END)
> AS TOPUP_MSISDN
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a3 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' THEN L.AUDIENCEKEY ELSE
> '' END) AS REDEEM_MSISDN
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a4 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '20' THEN L.AUDIENCEKEY ELSE null END) AS
> REDEEM_MSISDN_20
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a5 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '30' THEN L.AUDIENCEKEY ELSE null END) AS
> REDEEM_MSISDN_30
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a6 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '50' THEN L.AUDIENCEKEY ELSE null END) AS
> REDEEM_MSISDN_50
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a7 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '100' THEN L.AUDIENCEKEY ELSE null END) AS
> REDEEM_MSISDN_100
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a8 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN
> L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_20
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a9 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN
> L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_30
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a10 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN
> L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_50
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a11 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN
> L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_100
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> SELECT
> COALESCE(a1.LUCKY_DATE, a12.LUCKY_DATE) as LUCKY_DATE,
> COALESCE(a12.TOTAL_REGISTED, last_value(a12.TOTAL_REGISTED) OVER (order by
> COALESCE(a1.LUCKY_DATE, a12.LUCKY_DATE) ASC)) AS TOTAL_REGISTED,
> COALESCE(a12.TODAY_REGISTED, 0) AS TODAY_REGISTED,
> COALESCE(a1.TOTAL_MSISDN, 0) AS TOTAL_MSISDN,
> COALESCE(a1.TOPUP_AMOUNT_20, 0) AS TOPUP_AMOUNT_20,COALESCE(a1.TOPUP_AMOUNT_30,
> 0) AS TOPUP_AMOUNT_30, COALESCE(a1.TOPUP_AMOUNT_50, 0) AS TOPUP_AMOUNT_50,
> COALESCE(a1.TOPUP_AMOUNT_100, 0) AS TOPUP_AMOUNT_100,
> COALESCE(a1.TOPUP_AMOUNT, 0) AS TOPUP_AMOUNT,
> COALESCE(a1.FIRSTCALL_COUNT, 0) AS FIRSTCALL_COUNT,
> COALESCE(a1.LUCKYCODE_COUNT, 0) AS LUCKYCODE_COUNT,
> COALESCE(a1.BONUS_AMOUNT_20, 0) AS BONUS_AMOUNT_20,COALESCE(a1.BONUS_AMOUNT_30,
> 0) AS BONUS_AMOUNT_30,COALESCE(a1.BONUS_AMOUNT_50, 0) AS BONUS_AMOUNT_50,
> COALESCE(a1.BONUS_AMOUNT_100, 0) AS BONUS_AMOUNT_100,
> COALESCE(a1.TOTAL_BONUS_AMOUNT, 0) AS TOTAL_BONUS_AMOUNT,
> COALESCE(a1.REDEEM_AMOUNT_20, 0) AS REDEEM_AMOUNT_20,COALESCE(a1.REDEEM_AMOUNT_30,
> 0) AS REDEEM_AMOUNT_30, COALESCE(a1.REDEEM_AMOUNT_50, 0) AS
> REDEEM_AMOUNT_50, COALESCE(a1.REDEEM_AMOUNT_100, 0) AS REDEEM_AMOUNT_100,
> COALESCE(a1.REDEEM_AMOUNT, 0) AS REDEEM_AMOUNT,
> COALESCE(a4.REDEEM_MSISDN_20, 0) AS REDEEM_MSISDN_20,COALESCE(a5.REDEEM_MSISDN_30,
> 0) AS REDEEM_MSISDN_30,COALESCE(a6.REDEEM_MSISDN_50, 0) AS
> REDEEM_MSISDN_50,COALESCE(a7.REDEEM_MSISDN_100, 0) AS REDEEM_MSISDN_100,
> COALESCE(a3.REDEEM_MSISDN, 0) AS REDEEM_MSISDN,
> COALESCE(a1.REDEEM_TRAN_20, 0) AS REDEEM_TRAN_20,COALESCE(a1.REDEEM_TRAN_30,
> 0) AS REDEEM_TRAN_30,COALESCE(a1.REDEEM_TRAN_50, 0) AS
> REDEEM_TRAN_50,COALESCE(a1.REDEEM_TRAN_100, 0) AS REDEEM_TRAN_100,
> COALESCE(a1.REDEEM_TRAN, 0) AS REDEEM_TRAN,
> COALESCE(a1.TOPUP_COUNT_20, 0) AS TOPUP_COUNT_20,COALESCE(a1.TOPUP_COUNT_30,
> 0) AS TOPUP_COUNT_30,COALESCE(a1.TOPUP_COUNT_50, 0) AS
> TOPUP_COUNT_50,COALESCE(a1.TOPUP_COUNT_100, 0) AS TOPUP_COUNT_100,
> COALESCE(a1.TOPUP_COUNT, 0) AS TOPUP_COUNT,
> COALESCE(a8.TOPUP_MSISDN_20, 0) AS TOPUP_MSISDN_20,COALESCE(a9.TOPUP_MSISDN_30,
> 0) AS TOPUP_MSISDN_30,COALESCE(a10.TOPUP_MSISDN_50, 0) AS
> TOPUP_MSISDN_50,COALESCE(a11.TOPUP_MSISDN_100, 0) AS TOPUP_MSISDN_100,
> COALESCE(a2.TOPUP_MSISDN, 0) AS TOPUP_MSISDN,
> COALESCE(a1.TOPUP_TRAN_20, 0) AS TOPUP_TRAN_20, COALESCE(a1.TOPUP_TRAN_30,
> 0) AS TOPUP_TRAN_30, COALESCE(a1.TOPUP_TRAN_50, 0) AS TOPUP_TRAN_50,
> COALESCE(a1.TOPUP_TRAN_100, 0) AS TOPUP_TRAN_100, COALESCE(a1.TOPUP_TRAN ,
> 0) AS TOPUP_TRAN,
> COALESCE(a13.TODAY_REGISTED_SUCC, 0) TODAY_REGISTED_SUCC,
> COALESCE(a14.REDEEM_SUCC, 0 ) AS REDEEM_SUCC,
> COALESCE(a14.REDEEM_FAIL, 0 ) AS REDEEM_FAIL,
> COALESCE(a15.sum_input, 0 ) aS SUM_INPUT,
> COALESCE(a15.sum_output, 0 ) aS sum_output
> from a1
> FULL OUTER JOIN unica.v_Today_Reg a12 on a1.LUCKY_DATE = a12.LUCKY_DATE
> FUll OUTER JOIN unica.v_Today_Reg_Succ a13 on a1.LUCKY_DATE= a13.LUCKY_DATE
> FUll OUTER JOIN unica.V_Redeem a14 on a1.LUCKY_DATE= a14.LUCKY_DATE
> FULL JOIN unica.V_Eligible a15 on a1.LUCKY_DATE = a15.PROCESS_DATE
> FULL JOIN a2 on a1.LUCKY_DATE = a2.LUCKY_DATE
> FULL JOIN a3 on a1.LUCKY_DATE = a3.LUCKY_DATE
> FULL JOIN a4 on a1.LUCKY_DATE = a4.LUCKY_DATE
> FULL JOIN a5 on a1.LUCKY_DATE = a5.LUCKY_DATE
> FULL JOIN a6 on a1.LUCKY_DATE = a6.LUCKY_DATE
> FULL JOIN a7 on a1.LUCKY_DATE = a7.LUCKY_DATE
> FULL JOIN a8 on a1.LUCKY_DATE = a8.LUCKY_DATE
> FULL JOIN a9 on a1.LUCKY_DATE = a9.LUCKY_DATE
> FULL JOIN a10 on a1.LUCKY_DATE = a10.LUCKY_DATE
> FULL JOIN a11 on a1.LUCKY_DATE = a11.LUCKY_DATE
> where cast(a1.LUCKY_DATE as timestamp) = cast('2016-09-09' as timestamp)
>
>
>
> Best Regards,
> Siksit Santirojanakul (Eak)
> IT - CIS Customer Delivery / Campaign
> True Info Tech Co., Ltd.
> Tel. 02-699-7428
>
>
>
> Important
> Confidentiality: This Information is intended for the above-named person
> and may contain confidential and/or legally privileged material. Any
> opinions expressed in this information are not necessarily those of the
> company. If it has come to you in error you must take no action based on
> it, nor must you copy or show it to anyone; please delete/destroy and
> inform the sender immediately.
>
> Monitoring/Viruses
> True Corporation and subsidiaries reserves the right to monitor all
> incoming and outgoing emails via True Corporation and subsidiaries's
> systems. Although we have security program to monitor and eliminate virus,
> we also advise that in keeping with good computing practice the recipient
> should ensure they are actually virus free.
>
>
> Important
> Confidentiality: This Information is intended for the above-named person and may contain confidential and/or legally privileged material. Any opinions expressed in this information are not necessarily those of the company. If it has come to you in error you must take no action based on it, nor must you copy or show it to anyone; please delete/destroy and inform the sender immediately.
>
> Monitoring/Viruses
> True Corporation and subsidiaries reserves the right to monitor all incoming and outgoing emails via True Corporation and subsidiaries's systems. Although we have security program to monitor and eliminate virus, we also advise that in keeping with good computing practice the recipient should ensure they are actually virus free.
>
>
Re: COUNT(DISTINCT col) returns wrong results: Impala ODBC: Error
Posted by Si...@truecorp.co.th.
Dear All,
for the later versions impala or new version odbc for impala ? If new
version for impala have a big problem for deploy on my produciton!!!!
Best Regards,
Siksit Santirojanakul (Eak)
IT - CIS Customer Delivery / Campaign
True Info Tech Co., Ltd.
Tel. 02-699-7428
From: Tim Armstrong <ta...@cloudera.com>
To: user@impala.incubator.apache.org
Cc: user@impala.apache.org, user-subscribe@impala.incubator.apache.org
Date: 24/09/2016 06:46
Subject: Re: COUNT(DISTINCT col) returns wrong results: Impala
ODBC: Error
It's probably one of the multiple bugs along this line that have been
fixed in later versions of Impala. E.g.
https://issues.cloudera.org/browse/IMPALA-2216?filter=11799
On Thu, Sep 22, 2016 at 8:44 PM, <Si...@truecorp.co.th> wrote:
retry
Best Regards,
Siksit Santirojanakul (Eak)
IT - CIS Customer Delivery / Campaign
True Info Tech Co., Ltd.
Tel. 02-699-7428
From: Siksit Santirojanakul/True Corp
To: user@impala.incubator.apache.org
Date: 21/09/2016 14:54
Subject: Fw: COUNT(DISTINCT col) returns wrong results: Impala
ODBC: Error
Dear All,
Have problem from impala Impala 2.2.2 , odbc driver for Impala
2.05.28.1008. Error msg "Exception: DBD[Cloudera][ImpalaODBC] (110) Error
while excuting a query in Impala: [HY000]: illegalState: Illegal reference
to no-materialized slot: tid=0 sid=5 State: S1000"
My Sql Query:
with a1 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
, COUNT(DISTINCT L.AUDIENCEKEY) AS TOTAL_MSISDN
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0
END) AS TOPUP_COUNT_20
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0
END) AS TOPUP_COUNT_30
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0
END) AS TOPUP_COUNT_50
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0
END) AS TOPUP_COUNT_100
, SUM(CASE WHEN CDR_TYPE = 1 THEN 1 ELSE 0 END) AS TOPUP_COUNT
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN
TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_20
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN
TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_30
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN
TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_50
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN
TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_100
, SUM(CASE WHEN CDR_TYPE = 1 THEN TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT
, SUM(CASE WHEN CDR_TYPE = 2 THEN 1 ELSE 0 END) AS FIRSTCALL_COUNT
, COUNT(*) AS LUCKYCODE_COUNT
, SUM(CASE WHEN trim(PROVISION_ID) <> '' THEN BONUS_AMOUNT ELSE 0 END) AS
REDEEM_AMOUNT
, SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0 END) AS REDEEM_TRAN_20
, SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0 END) AS REDEEM_TRAN_30
, SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0 END) AS REDEEM_TRAN_50
, SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0 END) AS REDEEM_TRAN_100
, SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 THEN 1 ELSE 0
END) AS REDEEM_TRAN
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0
END) AS TOPUP_TRAN_20
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0
END) AS TOPUP_TRAN_30
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0
END) AS TOPUP_TRAN_50
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0
END) AS TOPUP_TRAN_100
, SUM(CASE WHEN CDR_TYPE = 1 THEN 1 ELSE 0 END) AS TOPUP_TRAN
, SUM(CASE WHEN trim(PROVISION_ID) <> '' THEN 1 ELSE 0 END) AS
REDEEM_COUNT
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN
BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_20
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN
BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_30
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN
BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_50
, SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN
BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_100
, SUM(CASE WHEN CDR_TYPE = 1 THEN BONUS_AMOUNT ELSE 0 END) AS
TOTAL_BONUS_AMOUNT
, SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
F.PROVISIONPARAM4 = '20'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_20
, SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
F.PROVISIONPARAM4 = '30'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_30
, SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
F.PROVISIONPARAM4 = '50'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_50
, SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
F.PROVISIONPARAM4 = '100'THEN BONUS_AMOUNT ELSE 0 END) AS
REDEEM_AMOUNT_100
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a2 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 THEN L.AUDIENCEKEY ELSE NULL END)
AS TOPUP_MSISDN
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a3 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' THEN L.AUDIENCEKEY ELSE
'' END) AS REDEEM_MSISDN
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a4 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '20' THEN L.AUDIENCEKEY ELSE null END) AS
REDEEM_MSISDN_20
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a5 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '30' THEN L.AUDIENCEKEY ELSE null END) AS
REDEEM_MSISDN_30
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a6 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '50' THEN L.AUDIENCEKEY ELSE null END) AS
REDEEM_MSISDN_50
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a7 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
F.PROVISIONPARAM4 = '100' THEN L.AUDIENCEKEY ELSE null END) AS
REDEEM_MSISDN_100
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a8 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN
L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_20
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a9 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN
L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_30
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a10 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN
L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_50
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
,a11 as (
SELECT
TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN
L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_100
FROM unica.BM_LUCKYDRAW L
INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
WHERE L.CAMPAIGNCODE IN('C000000301')
GROUP BY TO_DATE(INSERT_DATE)
)
SELECT
COALESCE(a1.LUCKY_DATE, a12.LUCKY_DATE) as LUCKY_DATE,
COALESCE(a12.TOTAL_REGISTED, last_value(a12.TOTAL_REGISTED) OVER (order by
COALESCE(a1.LUCKY_DATE, a12.LUCKY_DATE) ASC)) AS TOTAL_REGISTED,
COALESCE(a12.TODAY_REGISTED, 0) AS TODAY_REGISTED,
COALESCE(a1.TOTAL_MSISDN, 0) AS TOTAL_MSISDN,
COALESCE(a1.TOPUP_AMOUNT_20, 0) AS
TOPUP_AMOUNT_20,COALESCE(a1.TOPUP_AMOUNT_30, 0) AS TOPUP_AMOUNT_30,
COALESCE(a1.TOPUP_AMOUNT_50, 0) AS TOPUP_AMOUNT_50,
COALESCE(a1.TOPUP_AMOUNT_100, 0) AS TOPUP_AMOUNT_100,
COALESCE(a1.TOPUP_AMOUNT, 0) AS TOPUP_AMOUNT,
COALESCE(a1.FIRSTCALL_COUNT, 0) AS FIRSTCALL_COUNT,
COALESCE(a1.LUCKYCODE_COUNT, 0) AS LUCKYCODE_COUNT,
COALESCE(a1.BONUS_AMOUNT_20, 0) AS
BONUS_AMOUNT_20,COALESCE(a1.BONUS_AMOUNT_30, 0) AS
BONUS_AMOUNT_30,COALESCE(a1.BONUS_AMOUNT_50, 0) AS BONUS_AMOUNT_50,
COALESCE(a1.BONUS_AMOUNT_100, 0) AS BONUS_AMOUNT_100,
COALESCE(a1.TOTAL_BONUS_AMOUNT, 0) AS TOTAL_BONUS_AMOUNT,
COALESCE(a1.REDEEM_AMOUNT_20, 0) AS
REDEEM_AMOUNT_20,COALESCE(a1.REDEEM_AMOUNT_30, 0) AS REDEEM_AMOUNT_30,
COALESCE(a1.REDEEM_AMOUNT_50, 0) AS REDEEM_AMOUNT_50,
COALESCE(a1.REDEEM_AMOUNT_100, 0) AS REDEEM_AMOUNT_100,
COALESCE(a1.REDEEM_AMOUNT, 0) AS REDEEM_AMOUNT,
COALESCE(a4.REDEEM_MSISDN_20, 0) AS
REDEEM_MSISDN_20,COALESCE(a5.REDEEM_MSISDN_30, 0) AS
REDEEM_MSISDN_30,COALESCE(a6.REDEEM_MSISDN_50, 0) AS
REDEEM_MSISDN_50,COALESCE(a7.REDEEM_MSISDN_100, 0) AS REDEEM_MSISDN_100,
COALESCE(a3.REDEEM_MSISDN, 0) AS REDEEM_MSISDN,
COALESCE(a1.REDEEM_TRAN_20, 0) AS
REDEEM_TRAN_20,COALESCE(a1.REDEEM_TRAN_30, 0) AS
REDEEM_TRAN_30,COALESCE(a1.REDEEM_TRAN_50, 0) AS
REDEEM_TRAN_50,COALESCE(a1.REDEEM_TRAN_100, 0) AS REDEEM_TRAN_100,
COALESCE(a1.REDEEM_TRAN, 0) AS REDEEM_TRAN,
COALESCE(a1.TOPUP_COUNT_20, 0) AS
TOPUP_COUNT_20,COALESCE(a1.TOPUP_COUNT_30, 0) AS
TOPUP_COUNT_30,COALESCE(a1.TOPUP_COUNT_50, 0) AS
TOPUP_COUNT_50,COALESCE(a1.TOPUP_COUNT_100, 0) AS TOPUP_COUNT_100,
COALESCE(a1.TOPUP_COUNT, 0) AS TOPUP_COUNT,
COALESCE(a8.TOPUP_MSISDN_20, 0) AS
TOPUP_MSISDN_20,COALESCE(a9.TOPUP_MSISDN_30, 0) AS
TOPUP_MSISDN_30,COALESCE(a10.TOPUP_MSISDN_50, 0) AS
TOPUP_MSISDN_50,COALESCE(a11.TOPUP_MSISDN_100, 0) AS TOPUP_MSISDN_100,
COALESCE(a2.TOPUP_MSISDN, 0) AS TOPUP_MSISDN,
COALESCE(a1.TOPUP_TRAN_20, 0) AS TOPUP_TRAN_20, COALESCE(a1.TOPUP_TRAN_30,
0) AS TOPUP_TRAN_30, COALESCE(a1.TOPUP_TRAN_50, 0) AS TOPUP_TRAN_50,
COALESCE(a1.TOPUP_TRAN_100, 0) AS TOPUP_TRAN_100, COALESCE(a1.TOPUP_TRAN ,
0) AS TOPUP_TRAN,
COALESCE(a13.TODAY_REGISTED_SUCC, 0) TODAY_REGISTED_SUCC,
COALESCE(a14.REDEEM_SUCC, 0 ) AS REDEEM_SUCC,
COALESCE(a14.REDEEM_FAIL, 0 ) AS REDEEM_FAIL,
COALESCE(a15.sum_input, 0 ) aS SUM_INPUT,
COALESCE(a15.sum_output, 0 ) aS sum_output
from a1
FULL OUTER JOIN unica.v_Today_Reg a12 on a1.LUCKY_DATE = a12.LUCKY_DATE
FUll OUTER JOIN unica.v_Today_Reg_Succ a13 on a1.LUCKY_DATE=
a13.LUCKY_DATE
FUll OUTER JOIN unica.V_Redeem a14 on a1.LUCKY_DATE= a14.LUCKY_DATE
FULL JOIN unica.V_Eligible a15 on a1.LUCKY_DATE = a15.PROCESS_DATE
FULL JOIN a2 on a1.LUCKY_DATE = a2.LUCKY_DATE
FULL JOIN a3 on a1.LUCKY_DATE = a3.LUCKY_DATE
FULL JOIN a4 on a1.LUCKY_DATE = a4.LUCKY_DATE
FULL JOIN a5 on a1.LUCKY_DATE = a5.LUCKY_DATE
FULL JOIN a6 on a1.LUCKY_DATE = a6.LUCKY_DATE
FULL JOIN a7 on a1.LUCKY_DATE = a7.LUCKY_DATE
FULL JOIN a8 on a1.LUCKY_DATE = a8.LUCKY_DATE
FULL JOIN a9 on a1.LUCKY_DATE = a9.LUCKY_DATE
FULL JOIN a10 on a1.LUCKY_DATE = a10.LUCKY_DATE
FULL JOIN a11 on a1.LUCKY_DATE = a11.LUCKY_DATE
where cast(a1.LUCKY_DATE as timestamp) = cast('2016-09-09' as timestamp)
Best Regards,
Siksit Santirojanakul (Eak)
IT - CIS Customer Delivery / Campaign
True Info Tech Co., Ltd.
Tel. 02-699-7428
Important
Confidentiality: This Information is intended for the above-named person
and may contain confidential and/or legally privileged material. Any
opinions expressed in this information are not necessarily those of the
company. If it has come to you in error you must take no action based on
it, nor must you copy or show it to anyone; please delete/destroy and
inform the sender immediately.
Monitoring/Viruses
True Corporation and subsidiaries reserves the right to monitor all
incoming and outgoing emails via True Corporation and subsidiaries's
systems. Although we have security program to monitor and eliminate virus,
we also advise that in keeping with good computing practice the recipient
should ensure they are actually virus free.
Important
Confidentiality: This Information is intended for the above-named person and may contain confidential and/or legally privileged material. Any opinions expressed in this information are not necessarily those of the company. If it has come to you in error you must take no action based on it, nor must you copy or show it to anyone; please delete/destroy and inform the sender immediately.
Monitoring/Viruses
True Corporation and subsidiaries reserves the right to monitor all incoming and outgoing emails via True Corporation and subsidiaries's systems. Although we have security program to monitor and eliminate virus, we also advise that in keeping with good computing practice the recipient should ensure they are actually virus free.
Re: COUNT(DISTINCT col) returns wrong results: Impala ODBC: Error
Posted by Tim Armstrong <ta...@cloudera.com>.
It's probably one of the multiple bugs along this line that have been fixed
in later versions of Impala. E.g.
https://issues.cloudera.org/browse/IMPALA-2216?filter=11799
On Thu, Sep 22, 2016 at 8:44 PM, <Si...@truecorp.co.th> wrote:
> retry
>
>
> Best Regards,
> Siksit Santirojanakul (Eak)
> IT - CIS Customer Delivery / Campaign
> True Info Tech Co., Ltd.
> Tel. 02-699-7428
>
>
>
>
> From: Siksit Santirojanakul/True Corp
> To: user@impala.incubator.apache.org
> Date: 21/09/2016 14:54
> Subject: Fw: COUNT(DISTINCT col) returns wrong results: Impala
> ODBC: Error
> ------------------------------
>
>
> Dear All,
>
> Have problem from impala Impala 2.2.2 , odbc driver for Impala
> 2.05.28.1008. Error msg *"Exception: DBD[Cloudera][ImpalaODBC] (110)
> Error while excuting a query in Impala: [HY000]: illegalState: Illegal
> reference to no-materialized slot: tid=0 sid=5 State: S1000"*
>
> *My Sql Query: *
>
> with a1 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> , COUNT(DISTINCT L.AUDIENCEKEY) AS TOTAL_MSISDN
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0
> END) AS TOPUP_COUNT_20
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0
> END) AS TOPUP_COUNT_30
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0
> END) AS TOPUP_COUNT_50
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0
> END) AS TOPUP_COUNT_100
> , SUM(CASE WHEN CDR_TYPE = 1 THEN 1 ELSE 0 END) AS TOPUP_COUNT
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN
> TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_20
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN
> TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_30
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN
> TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_50
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN
> TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_100
> , SUM(CASE WHEN CDR_TYPE = 1 THEN TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT
> , SUM(CASE WHEN CDR_TYPE = 2 THEN 1 ELSE 0 END) AS FIRSTCALL_COUNT
> , COUNT(*) AS LUCKYCODE_COUNT
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' THEN BONUS_AMOUNT ELSE 0 END) AS
> REDEEM_AMOUNT
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0 END) AS REDEEM_TRAN_20
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0 END) AS REDEEM_TRAN_30
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0 END) AS REDEEM_TRAN_50
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0 END) AS REDEEM_TRAN_100
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 THEN 1 ELSE 0
> END) AS REDEEM_TRAN
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0
> END) AS TOPUP_TRAN_20
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0
> END) AS TOPUP_TRAN_30
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0
> END) AS TOPUP_TRAN_50
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0
> END) AS TOPUP_TRAN_100
> , SUM(CASE WHEN CDR_TYPE = 1 THEN 1 ELSE 0 END) AS TOPUP_TRAN
> , SUM(CASE WHEN trim(PROVISION_ID) <> '' THEN 1 ELSE 0 END) AS
> REDEEM_COUNT
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN
> BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_20
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN
> BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_30
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN
> BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_50
> , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN
> BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_100
> , SUM(CASE WHEN CDR_TYPE = 1 THEN BONUS_AMOUNT ELSE 0 END) AS
> TOTAL_BONUS_AMOUNT
> , SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
> F.PROVISIONPARAM4 = '20'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_20
> , SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
> F.PROVISIONPARAM4 = '30'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_30
> , SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
> F.PROVISIONPARAM4 = '50'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_50
> , SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND
> F.PROVISIONPARAM4 = '100'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_100
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a2 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 THEN L.AUDIENCEKEY ELSE NULL END)
> AS TOPUP_MSISDN
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a3 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' THEN L.AUDIENCEKEY ELSE
> '' END) AS REDEEM_MSISDN
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a4 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '20' THEN L.AUDIENCEKEY ELSE null END) AS
> REDEEM_MSISDN_20
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a5 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '30' THEN L.AUDIENCEKEY ELSE null END) AS
> REDEEM_MSISDN_30
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a6 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '50' THEN L.AUDIENCEKEY ELSE null END) AS
> REDEEM_MSISDN_50
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a7 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND
> F.PROVISIONPARAM4 = '100' THEN L.AUDIENCEKEY ELSE null END) AS
> REDEEM_MSISDN_100
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a8 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN
> L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_20
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a9 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN
> L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_30
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a10 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN
> L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_50
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> ,a11 as (
> SELECT
> TO_DATE(L.INSERT_DATE) AS LUCKY_DATE
> ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN
> L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_100
> FROM unica.BM_LUCKYDRAW L
> INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE
> AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
> AND F.AUDIENCEKEY = L.AUDIENCEKEY
> WHERE L.CAMPAIGNCODE IN('C000000301')
> GROUP BY TO_DATE(INSERT_DATE)
> )
> SELECT
> COALESCE(a1.LUCKY_DATE, a12.LUCKY_DATE) as LUCKY_DATE,
> COALESCE(a12.TOTAL_REGISTED, last_value(a12.TOTAL_REGISTED) OVER (order by
> COALESCE(a1.LUCKY_DATE, a12.LUCKY_DATE) ASC)) AS TOTAL_REGISTED,
> COALESCE(a12.TODAY_REGISTED, 0) AS TODAY_REGISTED,
> COALESCE(a1.TOTAL_MSISDN, 0) AS TOTAL_MSISDN,
> COALESCE(a1.TOPUP_AMOUNT_20, 0) AS TOPUP_AMOUNT_20,COALESCE(a1.TOPUP_AMOUNT_30,
> 0) AS TOPUP_AMOUNT_30, COALESCE(a1.TOPUP_AMOUNT_50, 0) AS TOPUP_AMOUNT_50,
> COALESCE(a1.TOPUP_AMOUNT_100, 0) AS TOPUP_AMOUNT_100,
> COALESCE(a1.TOPUP_AMOUNT, 0) AS TOPUP_AMOUNT,
> COALESCE(a1.FIRSTCALL_COUNT, 0) AS FIRSTCALL_COUNT,
> COALESCE(a1.LUCKYCODE_COUNT, 0) AS LUCKYCODE_COUNT,
> COALESCE(a1.BONUS_AMOUNT_20, 0) AS BONUS_AMOUNT_20,COALESCE(a1.BONUS_AMOUNT_30,
> 0) AS BONUS_AMOUNT_30,COALESCE(a1.BONUS_AMOUNT_50, 0) AS BONUS_AMOUNT_50,
> COALESCE(a1.BONUS_AMOUNT_100, 0) AS BONUS_AMOUNT_100,
> COALESCE(a1.TOTAL_BONUS_AMOUNT, 0) AS TOTAL_BONUS_AMOUNT,
> COALESCE(a1.REDEEM_AMOUNT_20, 0) AS REDEEM_AMOUNT_20,COALESCE(a1.REDEEM_AMOUNT_30,
> 0) AS REDEEM_AMOUNT_30, COALESCE(a1.REDEEM_AMOUNT_50, 0) AS
> REDEEM_AMOUNT_50, COALESCE(a1.REDEEM_AMOUNT_100, 0) AS REDEEM_AMOUNT_100,
> COALESCE(a1.REDEEM_AMOUNT, 0) AS REDEEM_AMOUNT,
> COALESCE(a4.REDEEM_MSISDN_20, 0) AS REDEEM_MSISDN_20,COALESCE(a5.REDEEM_MSISDN_30,
> 0) AS REDEEM_MSISDN_30,COALESCE(a6.REDEEM_MSISDN_50, 0) AS
> REDEEM_MSISDN_50,COALESCE(a7.REDEEM_MSISDN_100, 0) AS REDEEM_MSISDN_100,
> COALESCE(a3.REDEEM_MSISDN, 0) AS REDEEM_MSISDN,
> COALESCE(a1.REDEEM_TRAN_20, 0) AS REDEEM_TRAN_20,COALESCE(a1.REDEEM_TRAN_30,
> 0) AS REDEEM_TRAN_30,COALESCE(a1.REDEEM_TRAN_50, 0) AS
> REDEEM_TRAN_50,COALESCE(a1.REDEEM_TRAN_100, 0) AS REDEEM_TRAN_100,
> COALESCE(a1.REDEEM_TRAN, 0) AS REDEEM_TRAN,
> COALESCE(a1.TOPUP_COUNT_20, 0) AS TOPUP_COUNT_20,COALESCE(a1.TOPUP_COUNT_30,
> 0) AS TOPUP_COUNT_30,COALESCE(a1.TOPUP_COUNT_50, 0) AS
> TOPUP_COUNT_50,COALESCE(a1.TOPUP_COUNT_100, 0) AS TOPUP_COUNT_100,
> COALESCE(a1.TOPUP_COUNT, 0) AS TOPUP_COUNT,
> COALESCE(a8.TOPUP_MSISDN_20, 0) AS TOPUP_MSISDN_20,COALESCE(a9.TOPUP_MSISDN_30,
> 0) AS TOPUP_MSISDN_30,COALESCE(a10.TOPUP_MSISDN_50, 0) AS
> TOPUP_MSISDN_50,COALESCE(a11.TOPUP_MSISDN_100, 0) AS TOPUP_MSISDN_100,
> COALESCE(a2.TOPUP_MSISDN, 0) AS TOPUP_MSISDN,
> COALESCE(a1.TOPUP_TRAN_20, 0) AS TOPUP_TRAN_20, COALESCE(a1.TOPUP_TRAN_30,
> 0) AS TOPUP_TRAN_30, COALESCE(a1.TOPUP_TRAN_50, 0) AS TOPUP_TRAN_50,
> COALESCE(a1.TOPUP_TRAN_100, 0) AS TOPUP_TRAN_100, COALESCE(a1.TOPUP_TRAN ,
> 0) AS TOPUP_TRAN,
> COALESCE(a13.TODAY_REGISTED_SUCC, 0) TODAY_REGISTED_SUCC,
> COALESCE(a14.REDEEM_SUCC, 0 ) AS REDEEM_SUCC,
> COALESCE(a14.REDEEM_FAIL, 0 ) AS REDEEM_FAIL,
> COALESCE(a15.sum_input, 0 ) aS SUM_INPUT,
> COALESCE(a15.sum_output, 0 ) aS sum_output
> from a1
> FULL OUTER JOIN unica.v_Today_Reg a12 on a1.LUCKY_DATE = a12.LUCKY_DATE
> FUll OUTER JOIN unica.v_Today_Reg_Succ a13 on a1.LUCKY_DATE= a13.LUCKY_DATE
> FUll OUTER JOIN unica.V_Redeem a14 on a1.LUCKY_DATE= a14.LUCKY_DATE
> FULL JOIN unica.V_Eligible a15 on a1.LUCKY_DATE = a15.PROCESS_DATE
> FULL JOIN a2 on a1.LUCKY_DATE = a2.LUCKY_DATE
> FULL JOIN a3 on a1.LUCKY_DATE = a3.LUCKY_DATE
> FULL JOIN a4 on a1.LUCKY_DATE = a4.LUCKY_DATE
> FULL JOIN a5 on a1.LUCKY_DATE = a5.LUCKY_DATE
> FULL JOIN a6 on a1.LUCKY_DATE = a6.LUCKY_DATE
> FULL JOIN a7 on a1.LUCKY_DATE = a7.LUCKY_DATE
> FULL JOIN a8 on a1.LUCKY_DATE = a8.LUCKY_DATE
> FULL JOIN a9 on a1.LUCKY_DATE = a9.LUCKY_DATE
> FULL JOIN a10 on a1.LUCKY_DATE = a10.LUCKY_DATE
> FULL JOIN a11 on a1.LUCKY_DATE = a11.LUCKY_DATE
> where cast(a1.LUCKY_DATE as timestamp) = cast('2016-09-09' as timestamp)
>
>
>
> Best Regards,
> Siksit Santirojanakul (Eak)
> IT - CIS Customer Delivery / Campaign
> True Info Tech Co., Ltd.
> Tel. 02-699-7428
>
>
>
>
> Important
> Confidentiality: This Information is intended for the above-named person and may contain confidential and/or legally privileged material. Any opinions expressed in this information are not necessarily those of the company. If it has come to you in error you must take no action based on it, nor must you copy or show it to anyone; please delete/destroy and inform the sender immediately.
>
> Monitoring/Viruses
> True Corporation and subsidiaries reserves the right to monitor all incoming and outgoing emails via True Corporation and subsidiaries's systems. Although we have security program to monitor and eliminate virus, we also advise that in keeping with good computing practice the recipient should ensure they are actually virus free.
>
>