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.

Reply via email to