hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Haviv <daniel.ha...@veracity-group.com>
Subject Re: Case inside select statement in hive
Date Tue, 16 Dec 2014 17:11:02 GMT
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.vasudevan@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
>

Mime
View raw message