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
<verdier.jerom...@gmail.com>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 <verdier.jerom...@gmail.com>
>
>> 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 <sprag...@gmail.com>
>>
>>> 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
>>>>
>>>>
>>>
>>
>>
>> --
>> *Jérôme VERDIER*
>> 06.72.19.17.31
>> verdier.jerom...@gmail.com
>>
>>
>
>
> --
> *Jérôme VERDIER*
> 06.72.19.17.31
> verdier.jerom...@gmail.com
>
>
