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
