I am trying to convert the below teradata sql to hive sql but am getting error near the end line :ParseException line 83:6 missing ) at 'qualify' near 'qualify' line 83:14 missing EOF at 'row_number' near 'qualify'
I am very new to hive . Any help would be greatly appreciated
select customer_id tier_cust_id,
support_segment tier_suppt_seg
from (select c.customer_id,
c.primary_email_name,
am_id,
am_name,
c.customer_first_name ,
c.customer_last_name ,
c.customer_primary_residence ,
c.CUSTOMER_USER_GROUP,
c.customer_flag1,
case when c.customer_primary_residence not in ('US', 'MX', 'CA') then 'ROW'
when ent.cust_id = bam.cust_id then 'Enterprise Support'
when smb.cust_id = bam.cust_id then 'Merchant Support'
when am.am_name like '%Merchant Support'
or am.am_name like '%Business Support'
or am.am_name = 'Sole Proprietor'
then 'Business Support'
else 'Currently Unassigned'
end support_segment,
case when support_segment = 'Enterprise Support' then 10
when support_segment = 'Merchant Support' then 5
when support_segment = 'Business Support' then 1
when support_segment = 'ROW' then 0
end segment_weight
from dw_acct_mgr bam
inner join dw_acc_mger am
on bam.acct_mgr_id = am.am_id
and pmod((am.flag1 / 1), 2 )= 0
and pmod((am.flag1 / 2) ,2) = 1
and pmod((am.flag1 / 16) ,2) = 1
and pmod((bam.acct_mgr_flag1 / 4),2) = 0
and pmod((bam.acct_mgr_flag1 / 8),2 )= 0
and am_name not in ('Enterprise Account',
'SMB'
)
inner join customer c
on bam.cust_id = c.customer_id
left outer join (select cust_id
from dw_acct_mgr
where acct_mgr_id in (select am_id
from dw_acc_mger
where am_name in ('Enterprise Account')
and pmod((flag1 / 1), 2) = 0
and pmod( (flag1 / 2) , 2) = 1
)
and pmod((acct_mgr_flag1 / 4), 2) = 0
and prod((acct_mgr_flag1 / 8) , 2) = 0
) ent
on bam.cust_id = ent.cust_id
left outer join (select cust_id
from dw_acct_mgr
where acct_mgr_id in (select am_id
from dw_acc_mger
where am_name in ('SMB')
and pmod((flag1 / 1), 2) = 0
and pmod((flag1 / 1), 2) = 1
)
and pmod((acct_mgr_flag1 / 4) ,2) = 0
and pmod((acct_mgr_flag1 / 8),2) = 0
) smb
on bam.cust_id = smb.cust_id
qualify row_number() over (partition by customer_id order by segment_weight desc, am_name asc) = 1
) a;
QUALIFY is proprietary Teradata syntax, you must rewrite it using a Derived Table:
select customer_id tier_cust_id,
support_segment tier_suppt_seg
from (select c.customer_id,
c.primary_email_name,
am_id,
am_name,
c.customer_first_name ,
c.customer_last_name ,
c.customer_primary_residence ,
c.CUSTOMER_USER_GROUP,
c.customer_flag1,
case when c.customer_primary_residence not in ('US', 'MX', 'CA') then 'ROW'
when ent.cust_id = bam.cust_id then 'Enterprise Support'
when smb.cust_id = bam.cust_id then 'Merchant Support'
when am.am_name like '%Merchant Support'
or am.am_name like '%Business Support'
or am.am_name = 'Sole Proprietor'
then 'Business Support'
else 'Currently Unassigned'
end support_segment,
case when support_segment = 'Enterprise Support' then 10
when support_segment = 'Merchant Support' then 5
when support_segment = 'Business Support' then 1
when support_segment = 'ROW' then 0
end segment_weight,
row_number() over (partition by customer_id order by segment_weight desc, am_name asc) as rn
from dw_acct_mgr bam
inner join dw_acc_mger am
on bam.acct_mgr_id = am.am_id
and pmod((am.flag1 / 1), 2 )= 0
and pmod((am.flag1 / 2) ,2) = 1
and pmod((am.flag1 / 16) ,2) = 1
and pmod((bam.acct_mgr_flag1 / 4),2) = 0
and pmod((bam.acct_mgr_flag1 / 8),2 )= 0
and am_name not in ('Enterprise Account',
'SMB'
)
inner join customer c
on bam.cust_id = c.customer_id
left outer join (select cust_id
from dw_acct_mgr
where acct_mgr_id in (select am_id
from dw_acc_mger
where am_name in ('Enterprise Account')
and pmod((flag1 / 1), 2) = 0
and pmod( (flag1 / 2) , 2) = 1
)
and pmod((acct_mgr_flag1 / 4), 2) = 0
and prod((acct_mgr_flag1 / 8) , 2) = 0
) ent
on bam.cust_id = ent.cust_id
left outer join (select cust_id
from dw_acct_mgr
where acct_mgr_id in (select am_id
from dw_acc_mger
where am_name in ('SMB')
and pmod((flag1 / 1), 2) = 0
and pmod((flag1 / 1), 2) = 1
)
and pmod((acct_mgr_flag1 / 4) ,2) = 0
and pmod((acct_mgr_flag1 / 8),2) = 0
) smb
on bam.cust_id = smb.cust_id
) a
where rn = 1;