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
Hi,

Is it possible to have multiple conditions in Having clause ?

I get an error when i'm trying this.

Thanks a lot.
2013/8/30 Jérôme Verdier <[EMAIL PROTECTED]>

> Thanks Stephen,
>
> Yes i realise that it was so a stupid question....Maybe i wasn't really
> awaked this morning ;-)
>
> now it's working well.
>
> Thanks everyone.
>
>
> 2013/8/30 Stephen Sprague <[EMAIL PROTECTED]>
>
>> 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
*Jérôme VERDIER*
06.72.19.17.31
[EMAIL PROTECTED]