Skip to Content
-1
Jun 25, 2018 at 10:45 AM

Filter Window Function (QUALIFY)

739 Views

I have to migrate the following query from Teradata to Hana:

select 
  CO_EXPEDIENTE, 
  DT_FECHA , CO_RESULTADO,
  CO_CLIENTE,
  EMAIL, 
  row_number() over (partition by CO_EXPEDIENTE, CO_CLIENTE order by DT_FECHA desc) as orden
from "_CLIENTES"."PPU_ARCO"
where CO_MOTIVO_EXPEDIENTE = '10'
  and CO_TIPO_EXPEDIENTE is not null
  and CO_TIPO_EXPEDIENTE NOT IN (
    'AA',                -- Alta 
    'BB'                 -- Baja
  )
  and CO_RESULTADO = 'OK'
  and CO_CLIENTE is not null
qualify row_number() over (partition by CO_EXPEDIENTE, CO_CLIENTE order by DT_FECHA desc)  = 1

I got an error when I use the "qualify". The same query without qualify option works ok. Do you know about the best way to migrate this query to HANA?