Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
there should be no limitation AFAIK.   example + error message is always
helpful - even if you might regret it. :)
On Fri, Aug 30, 2013 at 8:16 AM, Jérôme Verdier
<[EMAIL PROTECTED]>wrote:

> 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
>>>>>
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB