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.
>
