Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Problème with min function in HiveQL


Copy link to this message
-
Re: Problème with min function in HiveQL
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
<[EMAIL PROTECTED]>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 <[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