I have two tables, which I want to join
For orders that have no deals i.e order with a 'Status' of 'CANCELED', I want to find the next deal (i.e the deal that came after the order sent date) for the same ccy_pair and buyOrSell column values.
select deals.deal_date as d_date, orders.order_id ,orders.buysell, orders.ccy_pair, orders.amount as o_amount, orders.rate as o_rate,
deals.amount as d_amount, deals.rate as d_rate
inner join deals on
and orders.status = 'CANCELED'
--trying to find deal
and deals.id = (SELECT id
FROM (SELECT id,
rank() over (partition by ccy_pair,buysell
order by deal_date) rnk
FROM deals where ccy_pair= orders.ccy_pair and deal_date > orders.sent_date
and buysell = orders.buysell
WHERE rnk = 1)
order by orders.sent_date desc
This is my beat attempt but I keep getting exceptions. Any help please?