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 <[email protected]>
> indeed. you nailed it.
>
>
> On Thu, Aug 29, 2013 at 11:53 AM, John Meagher <[email protected]>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 <[email protected]>
>> 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 <[email protected]
>> >
>> > 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
[email protected]
