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