QUALIFY equivalent in HIVE / SPARK SQL

I am trying to convert a Teradata SQL query into HIVE/Spark SQL equivalent. Is there any substitute for qualify along with count

SELECT
PARENT_ID, CHILD_ID
from db.source_table
QUALIFY COUNT(CHILD_ID) OVER (PARTITION BY  PARENT_ID)=0

​x
 
SELECTPARENT_ID, CHILD_IDfrom db.source_tableQUALIFY COUNT(CHILD_ID) OVER (PARTITION BY  PARENT_ID)=0​

Answer

Got it 🙂

select 
PARENT_ID, 
CHILD_ID
from 
(select PARENT_ID, CHILD_ID, 
    count(CHILD_ID) over (partition by PARENT_ID order by PARENT_ID asc) as COUNT_PARENT_ID 
    from db.source_table
) where COUNT_PARENT_ID = 0

 
select PARENT_ID, CHILD_IDfrom (select PARENT_ID, CHILD_ID,     count(CHILD_ID) over (partition by PARENT_ID order by PARENT_ID asc) as COUNT_PARENT_ID     from db.source_table) where COUNT_PARENT_ID = 0​

Advertisement

Answer