awright Jerome. look closely at the error message. you can figure this one
out.
On Fri, Aug 30, 2013 at 1:17 AM, Jérôme Verdier
<verdier.jerom...@gmail.com>wrote:
> 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
>
>
