hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gayathri Swaroop <g3.vasude...@gmail.com>
Subject Case inside select statement in hive
Date Tue, 16 Dec 2014 16:37:54 GMT
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