gistfile1.txt

 create temp table event_shp
 as (
 select cust_key,
 case when delivery_channel <> 'JOIN' and
           product_type = 'General' and
           sale_dt in
           (select event_dt
           from user_tbls.events
           where anniversary_public_event=1 ) then 1
      else 0
      end as Anniversary_Public,
 case when delivery_channel <> 'JOIN' and
           product_type = 'General' and
           sale_dt in
           (select event_dt
           from user_tbls.events
           where anniversary_early_access=1) then 1
      else 0
      end as Anniversary_EA,
 from user_tbls.transitory
 );

^^ ORIGINAL TABLE WITH SUBQUERY WHICH DOESN'T WORK IN HIVE SQL. THEN MAYBE:

#MODIFIED STATMENT WITHOUT SUBQUERY ADDING SALE_DT TO SELECT, WORKS IN HIVE SQL

create temp table event_shp
 as (
 select cust_key,
 sale_dt,
 case when delivery_channel <> 'JOIN' and
           product_type = 'General' then 1
      else 0
      end as Anniversary_Public,
 case when delivery_channel <> 'JOIN' and
           product_type = 'General' then 1
      else 0
      end as Anniversary_EA,
 from user_tbls.transitory
);

Then the table event_shp has Anniversary_Public and Anniversary_EA with values of either 1 or 0. What I think I want to do is look at the table user_tbls.events from the subquery possibly a JOIN on sale_dt=event_dt and set Anniversary_Public and Anniversary_EA to the "boolean AND" between them and the corresponding anniversary_public_event or anniversary_early_access. EG. 1&1 = 1 and all others 0.

totally looking for bitwise ops: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-and-transact-sql

Given modified table without subquery above maybe something like :

select * from event_shp E left join select event_dt,anniversary_public_event,anniversary_early_access from user_tbls.events T
 CASE WHEN E.sale_dt=T.event_dt
   then E.AnniversaryPublic & T.anniversary_public_event as E.Anniversary_Public and E.Anniversary_EA & T.anniversary_early_access as E.Anniversary_EA
 else 0 as E.Anniversary_Public and 0 as E.Anniversary_EA;

select cust_key,
 case when delivery_channel <> 'JOIN' and
           product_type = 'General' and
            anniversary_public_event=1 ) then 1
      else 0
      end as Anniversary_Public,
 case when delivery_channel <> 'JOIN' and
           product_type = 'General' and
            anniversary_early_access=1) then 1
      else 0
      end as Anniversary_EA,
 from user_tbls.transitory left join user_tbls.events on sale_dt = event_dt