Ok, thanks for this solution. Unfortunately, i have rewrited my script like this :
INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM SELECT '${hiveconf:in_co_societe}' as co_societe, '${hiveconf:in_co_an_semaine}' as co_an_semaine, a.type_entite as type_entite, a.code_entite as code_entite, a.type_rgrp_produits as type_rgrp_produits, a.co_rgrp_produits as co_rgrp_produits, SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC, SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC, SUM(a.NB_CLIENTS) as NB_CLIENTS, SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP, SUM(a.MT_OBJ_CA_NET_TTC_COMP) as MT_OBJ_CA_NET_TTC_COMP, SUM(a.NB_CLIENTS_COMP) as NB_CLIENTS_COMP from default.THM_CA_RGRP_PRODUITS_JOUR a JOIN default.CALENDRIER b -- A verifier WHERE a.co_societe = '${hiveconf:in_co_societe}' AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG') GROUP BY a.type_entite, a.code_entite, a.type_rgrp_produits, a.co_rgrp_produits; HAVING a.dt_jour >= MIN(b.dt_jour) AND a.dt_jour < MIN(b.dt_jour)+1 And i have a new error : FAILED: ParseException line 2:6 cannot recognize input near 'HAVING' 'a' '.' My Hive version is 0.11 and HAVING function have been implemented in 0.7 so it would work fine. 2013/8/29 Stephen Sprague <sprag...@gmail.com> > indeed. you nailed it. > > > On Thu, Aug 29, 2013 at 11:53 AM, John Meagher <john.meag...@gmail.com>wrote: > >> Aggregate functions need to go in a HAVING clause instead of the WHERE >> clause. WHERE clauses are applied prior to aggregation, HAVING is >> applied post aggregation. >> >> select ... >> from ... >> where some row level filter >> group by ... >> having some aggregate level filter >> >> >> On Thu, Aug 29, 2013 at 2:49 PM, Jason Dere <jd...@hortonworks.com> >> wrote: >> > Looks like the issue is the use of min() within the WHERE clause - the >> place >> > where the exception is being thrown has the following comment: >> > // UDAF in filter condition, group-by caluse, param of funtion, >> etc. >> > >> > >> > On Aug 29, 2013, at 3:01 AM, Jérôme Verdier <verdier.jerom...@gmail.com >> > >> > wrote: >> > >> > Hi everybody, >> > >> > I am coding some HiveQL script to do some calculations. >> > >> > I have a problem with the min() function. >> > >> > My hive script is below : >> > >> > INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM >> > >> > SELECT >> > '${hiveconf:in_co_societe}' as co_societe, >> > '${hiveconf:in_co_an_semaine}' as co_an_semaine, >> > a.type_entite as type_entite, >> > a.code_entite as code_entite, >> > a.type_rgrp_produits as type_rgrp_produits, >> > a.co_rgrp_produits as co_rgrp_produits, >> > SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC, >> > SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC, >> > SUM(a.NB_CLIENTS) as NB_CLIENTS, >> > SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP, >> > SUM(a.MT_OBJ_CA_NET_TTC_COMP) as >> MT_OBJ_CA_NET_TTC_COMP, >> > SUM(a.NB_CLIENTS_COMP) as NB_CLIENTS_COMP >> > from >> > default.THM_CA_RGRP_PRODUITS_JOUR a >> > JOIN default.CALENDRIER b >> > -- A verifier >> > WHERE CAST(a.dt_jour AS TIMESTAMP) >= >> > CAST(min(UNIX_TIMESTAMP(b.dt_jour)) AS BIGINT) >> > AND CAST(a.dt_jour AS TIMESTAMP) < CAST(min(b.dt_jour)+1 AS >> TIMESTAMP) >> > AND a.co_societe = '${hiveconf:in_co_societe}' >> > AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG') >> > GROUP BY >> > a.type_entite, >> > a.code_entite, >> > a.type_rgrp_produits, >> > a.co_rgrp_produits; >> > >> > And, when i try to launch this, i get this error : >> > >> > FAILED: SemanticException [Error 10128]: Line 20:62 Not yet supported >> place >> > for UDAF 'min' >> > >> > Obviously, there is a problem with min() function. >> > >> > How can i pass through this error? >> > >> > Thanks for your help >> > >> > >> > >> > CONFIDENTIALITY NOTICE >> > NOTICE: This message is intended for the use of the individual or >> entity to >> > which it is addressed and may contain information that is confidential, >> > privileged and exempt from disclosure under applicable law. If the >> reader of >> > this message is not the intended recipient, you are hereby notified >> that any >> > printing, copying, dissemination, distribution, disclosure or >> forwarding of >> > this communication is strictly prohibited. If you have received this >> > communication in error, please contact the sender immediately and >> delete it >> > from your system. Thank You. >> > > -- *Jérôme VERDIER* 06.72.19.17.31 verdier.jerom...@gmail.com