I am trying to run a query within oracle which creates a value. The underlying table does not always contain a match for a specific date. So i have to either find the record with the date specified or find the closest date below the specified date.
SUM(CASE
WHEN S.VAL_SECURITY NOT IN ('GBP')
AND E.SECURITY_ID NOT IN ('GBP')
THEN (
(((amount) * - 1) / nvl(s.minor_per_unit, 1)) * (
SELECT EXCHANGE_RATE
FROM ISET.PREVIOUS_PRICES
WHERE ISET.PREVIOUS_PRICES.SECURITY_ID = E.SECURITY_ID
AND VAL_DATE = TO_DATE('05/08/2019', 'DD/MM/YYYY')
)
) * (
SELECT EXCHANGE_RATE
FROM ISET.PREVIOUS_PRICES
WHERE SECURITY_ID = S.VAL_SECURITY
AND VAL_DATE = TO_DATE('05/08/2019', 'DD/MM/YYYY')
)
WHEN S.VAL_SECURITY IN ('GBP')
AND E.SECURITY_ID NOT IN ('GBP')
THEN (
(((amount) * - 1) / nvl(s.minor_per_unit, 1)) * (
SELECT EXCHANGE_RATE
FROM ISET.PREVIOUS_PRICES
WHERE ISET.PREVIOUS_PRICES.SECURITY_ID = E.SECURITY_ID
AND VAL_DATE = TO_DATE('05/08/2019', 'DD/MM/YYYY')
)
)
ELSE ((amount) * - 1) / nvl(s.minor_per_unit, 1)
END) GBP_VALUE
The code in question is
E.SECURITY_ID AND VAL_DATE = TO_DATE('05/08/2019', 'DD/MM/YYYY')
So assuming it doesn't find a record for the specified date then it needs to find the max date record closest to but below the specified date.
Any help would be greatly appreciated.
Thank you
Could you try this? I haven't verified it.
val_date =
(SELECT val_date
FROM (SELECT MAX (b.val_date) val_date,
MIN (TO_DATE ('05/08/2019', 'DD/MM/YYYY') - b.val_date)
diff
FROM PREVIOUS_PRICES b
WHERE SECURITY_ID = E.SECURITY_ID
AND status_date <= TO_DATE ('05/08/2019', 'DD/MM/YYYY')
UNION ALL
SELECT MIN (val_date),
MIN (b.val_date - TO_DATE ('05/08/2019', 'DD/MM/YYYY'))
FROM PREVIOUS_PRICES b
WHERE SECURITY_ID = E.SECURITY_ID
AND status_date > TO_DATE ('05/08/2019', 'DD/MM/YYYY')
ORDER BY 2)
WHERE ROWNUM = 1)