Certainly a new version of Impala (not ODBC). Sorry this is causing you trouble.
On Sun, Sep 25, 2016 at 8:05 PM, <siksit_...@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 <tarmstr...@cloudera.com> > To: user@impala.incubator.apache.org > Cc: u...@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_...@truecorp.co.th* > <siksit_...@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* > <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. > >
