Hive NOT IN clause

[abhiTowson cal <ab...@gmail.com>]

hi all,
Does hive support NOT IN clause,if not what is the alternative for
that.My query is posted below

SELECT
me.EADDR_TXT as EMAIL_ADDR_TXT,
'' as SRC_CUST_ID,
'ACXIOM' as SRC_SYS_NM,
me.EMAIL_ADDR_ID as UCM_EMAIL_ADDR_ID,
me.EMKTG_EML_STAT_IND as EMKTG_EML_STAT_IND,
me.EMKTG_EML_STATR_CD as EMKTG_EML_STATR_CD,
me.LAST_UPD_TS as EMKTG_LAST_UPD_TS,
mporg.BUS_NM  as ORG_NM,
'' as PRSN_FRST_NM,
'' as PRSN_LAST_NM,
'' as PRSN_MID_NM,
'' as PRSN_TTL_NM,
'' as PRSN_SFX_NM,
mporg.ZIP_CD as ZIP_CD
FROM goldenrecord.MKTG_EADDR me
JOIN goldenrecord.MKTG_PRCSS_PREF mppref on me.EMAIL_ADDR_ID =
mppref.EMAIL_ADDR_ID
JOIN goldenrecord.MKTG_PRCSS_ORG  mporg on mporg.EMKTG_ORG_ID =
mppref.EMKTG_ORG_ID
where me.EMAIL_ADDR_ID NOT IN ('23')

Regards
Abhishek

Re: Hive NOT IN clause

[John Meagher <jo...@gmail.com>]

This syntax works:

where NOT (me.EMAIL_ADDR_ID IN ('23'))


On Fri, Jul 6, 2012 at 2:44 PM, abhiTowson cal
<ab...@gmail.com> wrote:
> hi all,
> Does hive support NOT IN clause,if not what is the alternative for
> that.My query is posted below
>
> SELECT
> me.EADDR_TXT as EMAIL_ADDR_TXT,
> '' as SRC_CUST_ID,
> 'ACXIOM' as SRC_SYS_NM,
> me.EMAIL_ADDR_ID as UCM_EMAIL_ADDR_ID,
> me.EMKTG_EML_STAT_IND as EMKTG_EML_STAT_IND,
> me.EMKTG_EML_STATR_CD as EMKTG_EML_STATR_CD,
> me.LAST_UPD_TS as EMKTG_LAST_UPD_TS,
> mporg.BUS_NM  as ORG_NM,
> '' as PRSN_FRST_NM,
> '' as PRSN_LAST_NM,
> '' as PRSN_MID_NM,
> '' as PRSN_TTL_NM,
> '' as PRSN_SFX_NM,
> mporg.ZIP_CD as ZIP_CD
> FROM goldenrecord.MKTG_EADDR me
> JOIN goldenrecord.MKTG_PRCSS_PREF mppref on me.EMAIL_ADDR_ID =
> mppref.EMAIL_ADDR_ID
> JOIN goldenrecord.MKTG_PRCSS_ORG  mporg on mporg.EMKTG_ORG_ID =
> mppref.EMKTG_ORG_ID
> where me.EMAIL_ADDR_ID NOT IN ('23')
>
> Regards
> Abhishek