title: Teradata conversion to hive sql
body: <p>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'</p>
  
  <p>I am very new to hive . Any help would be greatly appreciated</p>
  
  <pre><code>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;
  </code></pre>