Row Number in Spark SQL MV

[KHK]

Hi,

I have below Oracle Query and i want to convert the query to Spark SQL . Please let me know how to write Rownum condition in spark sql.

Sample Oracle Query:

select

pes.event_num,

glp.period_name,

(SELECT conversion_rate FROM gl_daily_rates
WHERE from_currency = pes.bill_trans_currency_code and to_currency = 'USD'
AND conversion_date = NVL(pes.projfunc_rate_date, glp.end_date)
AND ROWNUM < 2
) as conversion_rate

from

pa_periods_all glp,

pa_events pes

[Shashidhar]

Hi,

You can try like below,

SELECT
pes.event_num, glp.period_name, conversion_rate

from 
( select pes.event_num, glp.period_name,
ROW_NUMBER() OVER (
PARTITION BY pes.event_num, glp.period_name
ORDER BY gdr.conversion_date ) AS row_num 

from pa_periods_all glp join pa_events pes on {join column} 

join gl_daily_rates gdr

on gdr.from_currency = pes.bill_trans_currency_code 

and gdr.to_currency = 'USD'

and gdr.conversion_date = COALESCE(pes.projfunc_rate_date, glp.end_date)

) sp
WHERE sp.row_num <2

Regards,

Shashidhar.S

[dylanwan]

You can consider using LEFT OUTER JOIN if the table may not have a matching record.  
If there are multiple such SELECT clause lookup table, you can use CTE.

WITH conversion_rate_LKP AS (
  SELECT conversion_rate,
         ROW_NUMBER() OVER (PARTITION BY from_currency, conversion_date 
                            ORDER BY conversion_date) AS rn
  FROM gl_daily_rates
  WHERE to_currency = 'USD'
)
SELECT     pes.event_num,
           glp.period_name
FROM       pa_periods_all glp
JOIN       pa_events pes ON ...
LEFT JOIN conversion_rate_LKP L
       ON L.from_currency = pes.bill_trans_currency_code
      AND conversion_date = NVL(pes.projfunc_rate_date, glp.end_date
WHERE 
    L.rn = 1