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
