You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Gayathri Swaroop <g3...@gmail.com> on 2014/12/16 17:37:54 UTC
Case inside select statement in hive
Hi,
I have oracle query which i want to hit against a hive table.
The oracle query has a case if exists select
would this work in hive?
This is my oracle query that needs to be converted to hive.
select distinct CONTR.BMF_PARTNER_ID AS BMF_BMF_PARTNER_ID,
CONTR.BUSINESS_PARTNER AS BMF_BUS_PRTNR_ID,
CONTR.CONTRACT_ACCOUNT AS BMF_CONTR_ACCT_ID,
CONTR.CONTRACT_NBR AS BMF_CONTR_ID,
CONTR.ESIID AS BMF_ESI_ID,
CONTR.INSTALLATION_ID AS BMF_INSTALLATION_ID,
CONTR.SEGMENT_TYPE_CD AS BMF_SEGMENT_TYPE_CD,
CONTR.PARTNER_TYPE AS BMF_PARTNER_TYPE,
CONTR.ACTUAL_MOVEIN_DATE AS BMF_ACTUAL_MVI_DT,
CONTR.ACTUAL_MOVEOUT_DATE AS BMF_ACTUAL_MVO_DT,
CONTR.ENRL_RATE_CATEGORY AS BMF_ENRL_RATE_CATEGORY,
CONTR.CAMPAIGN_CD AS BMF_CAMPAIGN_CD,
CONTR.OFFER_CD AS BMF_OFFER_CD,
case when exists (select * from KSS_ACTIVITY_STG_CURR_STAT C_ID
where c_id.esiid = contr.esiid
and c_id.contract_nbr = contr.contract_nbr
and c_id.BMF_PARTNER_ID <> contr.BMF_PARTNER_ID
and c_id.partner_type=2
and c_id.actual_movein_date <
to_date('09/30/2014','mm/dd/yyyy')
and c_id.actual_moveout_date
>=to_date('09/30/2014','mm/dd/yyyy'))
then 'YES' else NULL end
as IS_DUPLICATE_BMF
FROM KSS_ESIID_LIST ESID INNER JOIN
KSS_ACTIVITY_STG_CURR_STAT CONTR ON
ESID.BMF_PARTNER_ID = CONTR.BMF_PARTNER_ID
WHERE contr.partner_type=2
and CONTR.actual_movein_date <
to_date('09/30/2014','mm/dd/yyyy')
and CONTR.actual_moveout_date
>=to_date('09/30/2014','mm/dd/yyyy');
Thanks,
Gayathri
Fwd: Case inside select statement in hive
Posted by Gayathri Swaroop <g3...@gmail.com>.
I use hive 0.12.0.
---------- Forwarded message ----------
From: Gayathri Swaroop <g3...@gmail.com>
Date: Tue, Dec 16, 2014 at 10:37 AM
Subject: Case inside select statement in hive
To: user <us...@hive.apache.org>
Hi,
I have oracle query which i want to hit against a hive table.
The oracle query has a case if exists select
would this work in hive?
This is my oracle query that needs to be converted to hive.
select distinct CONTR.BMF_PARTNER_ID AS BMF_BMF_PARTNER_ID,
CONTR.BUSINESS_PARTNER AS BMF_BUS_PRTNR_ID,
CONTR.CONTRACT_ACCOUNT AS BMF_CONTR_ACCT_ID,
CONTR.CONTRACT_NBR AS BMF_CONTR_ID,
CONTR.ESIID AS BMF_ESI_ID,
CONTR.INSTALLATION_ID AS BMF_INSTALLATION_ID,
CONTR.SEGMENT_TYPE_CD AS BMF_SEGMENT_TYPE_CD,
CONTR.PARTNER_TYPE AS BMF_PARTNER_TYPE,
CONTR.ACTUAL_MOVEIN_DATE AS BMF_ACTUAL_MVI_DT,
CONTR.ACTUAL_MOVEOUT_DATE AS BMF_ACTUAL_MVO_DT,
CONTR.ENRL_RATE_CATEGORY AS BMF_ENRL_RATE_CATEGORY,
CONTR.CAMPAIGN_CD AS BMF_CAMPAIGN_CD,
CONTR.OFFER_CD AS BMF_OFFER_CD,
case when exists (select * from KSS_ACTIVITY_STG_CURR_STAT C_ID
where c_id.esiid = contr.esiid
and c_id.contract_nbr = contr.contract_nbr
and c_id.BMF_PARTNER_ID <> contr.BMF_PARTNER_ID
and c_id.partner_type=2
and c_id.actual_movein_date <
to_date('09/30/2014','mm/dd/yyyy')
and c_id.actual_moveout_date
>=to_date('09/30/2014','mm/dd/yyyy'))
then 'YES' else NULL end
as IS_DUPLICATE_BMF
FROM KSS_ESIID_LIST ESID INNER JOIN
KSS_ACTIVITY_STG_CURR_STAT CONTR ON
ESID.BMF_PARTNER_ID = CONTR.BMF_PARTNER_ID
WHERE contr.partner_type=2
and CONTR.actual_movein_date <
to_date('09/30/2014','mm/dd/yyyy')
and CONTR.actual_moveout_date
>=to_date('09/30/2014','mm/dd/yyyy');
Thanks,
Gayathri
Re: Case inside select statement in hive
Posted by Daniel Haviv <da...@veracity-group.com>.
Hi,
Please RTFM before asking questions.
Taken from
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF:
Conditional Functions
*Return Type*
*Name(Signature)*
*Description*
T
if(boolean testCondition, T valueTrue, T valueFalseOrNull)
Returns valueTrue when testCondition is true, returns valueFalseOrNull
otherwise.
T
COALESCE(T v1, T v2, ...)
Returns the first v that is not NULL, or NULL if all v's are NULL.
T
CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
When a = b, returns c; when a = d, returns e; else returns f.
T
CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
When a = true, returns b; when c = true, returns d; else returns e.
BR,
Daniel
On Tue, Dec 16, 2014 at 6:37 PM, Gayathri Swaroop <g3...@gmail.com>
wrote:
>
> Hi,
>
> I have oracle query which i want to hit against a hive table.
> The oracle query has a case if exists select
> would this work in hive?
> This is my oracle query that needs to be converted to hive.
>
> select distinct CONTR.BMF_PARTNER_ID AS BMF_BMF_PARTNER_ID,
> CONTR.BUSINESS_PARTNER AS BMF_BUS_PRTNR_ID,
> CONTR.CONTRACT_ACCOUNT AS BMF_CONTR_ACCT_ID,
> CONTR.CONTRACT_NBR AS BMF_CONTR_ID,
> CONTR.ESIID AS BMF_ESI_ID,
> CONTR.INSTALLATION_ID AS BMF_INSTALLATION_ID,
> CONTR.SEGMENT_TYPE_CD AS BMF_SEGMENT_TYPE_CD,
> CONTR.PARTNER_TYPE AS BMF_PARTNER_TYPE,
> CONTR.ACTUAL_MOVEIN_DATE AS BMF_ACTUAL_MVI_DT,
> CONTR.ACTUAL_MOVEOUT_DATE AS BMF_ACTUAL_MVO_DT,
> CONTR.ENRL_RATE_CATEGORY AS BMF_ENRL_RATE_CATEGORY,
> CONTR.CAMPAIGN_CD AS BMF_CAMPAIGN_CD,
> CONTR.OFFER_CD AS BMF_OFFER_CD,
> case when exists (select * from KSS_ACTIVITY_STG_CURR_STAT C_ID
> where c_id.esiid = contr.esiid
> and c_id.contract_nbr = contr.contract_nbr
> and c_id.BMF_PARTNER_ID <> contr.BMF_PARTNER_ID
> and c_id.partner_type=2
> and c_id.actual_movein_date <
> to_date('09/30/2014','mm/dd/yyyy')
> and c_id.actual_moveout_date
> >=to_date('09/30/2014','mm/dd/yyyy'))
> then 'YES' else NULL end
> as IS_DUPLICATE_BMF
> FROM KSS_ESIID_LIST ESID INNER JOIN
> KSS_ACTIVITY_STG_CURR_STAT CONTR ON
> ESID.BMF_PARTNER_ID = CONTR.BMF_PARTNER_ID
> WHERE contr.partner_type=2
> and CONTR.actual_movein_date <
> to_date('09/30/2014','mm/dd/yyyy')
> and CONTR.actual_moveout_date
> >=to_date('09/30/2014','mm/dd/yyyy');
>
>
> Thanks,
> Gayathri
>