Using Cases for currency conversion in oracle

Question

I have two tables

Payments_history

CREATE TABLE payments_history(payment_date , account_id , currency, amount) AS
SELECT  TO_DATE ('05/01/2022','DD/MM/YYYY'), 2291969088, 'GBP', 10.00  FROM DUAL UNION ALL
SELECT TO_DATE ('05/01/2022','DD/MM/YYYY'), 7851880663, 'USD', 20  FROM DUAL UNION ALL
SELECT  TO_DATE ('06/01/2022','DD/MM/YYYY'), 5326844767, 'USD', 3.000  FROM DUAL UNION ALL
SELECT  TO_DATE ('05/01/2022','DD/MM/YYYY'), 3668657617, 'EUR', 40  FROM DUAL UNION ALL
SELECT  TO_DATE ('06/01/2022','DD/MM/YYYY'), 9040142052, 'GBP', 30.000  FROM DUAL 

Historics_rates

Create TABLE Historics_rates(t_date,from_ccy,to_ccy,rate) AS
SELECT  TO_DATE ('06/01/2022','DD/MM/YYYY'),'GBP','EUR',1.1832 FROM DUAL UNION ALL
SELECT  TO_DATE ('06/01/2022','DD/MM/YYYY'),'AUD','GBP',0.5263 FROM DUAL UNION ALL
SELECT  TO_DATE ('06/01/2022','DD/MM/YYYY'),'EUR','GBP',0.8452 FROM DUAL UNION ALL
SELECT  TO_DATE ('05/01/2022','DD/MM/YYYY'),'USD','GBP',0.7388 FROM DUAL UNION ALL
SELECT  TO_DATE ('05/01/2022','DD/MM/YYYY'),'EUR','USD',1.1441 FROM DUAL 

what I am trying to find is the 'daily amount GBP Equivalent per day for the last three months'. For example on date '05/01/2022 ' check the given amount in Payments_history table if the currency is GBP add it into total and move on to the next payment, next if the currency is in USD check for its equivalent in GBP rate and convert it into GBP and print the result. If the amount is in some currency whose from_ccy is not GBP skip that transaction

Date           Amount in GBP
2022-01-05        24.78 

This is what I have done so far

DECLARE

total NUMBER;

BEGIN
select PH.PAYMENT_DATE, 
CASE 
            WHEN PH.CURRENCY = 'GBP' THEN total = total + PH.AMOUNT
            WHEN PH.CURRENCY = 'USD' AND HR.from_ccy = 'USD' AND HR.to_ccy = 'GBP' THEN total = total + (PH.AMOUNT*HR."rate")
            WHEN PH.CURRENCY = 'AUD' AND HR.from_ccy = 'AUD' AND HR.to_ccy = 'GBP' THEN total = total + (PH.AMOUNT*HR."rate")
            WHEN PH.CURRENCY = 'EUR' AND HR.from_ccy = 'EUR' AND HR.to_ccy = 'GBP' THEN total = total + (PH.AMOUNT*HR."rate")
            ELSE 'CURRENCY NOT FOUND'
            END AS total
FROM "historic_rates" AS HR RIGHT JOIN PAYMENTS_HISTORY AS PH on HR."date" = PH.payment_date AND PH.Currency = HR."from_ccy"
WHERE Extract(Month from PH.payment_date) = Extract(month from add_months( sysdate, -3 )) GROUP BY PH.PAYMENT_DATE;

but it's giving me error

[Err] ORA-06550: line 8, column 40:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 15, column 117:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ( begin case declare end exception exit for goto if loop mod
   null pragma raise return select update while with
   <an identifier> <a double-quot

Answer 1

Your errors include:

• Using = inside the THEN clause of a CASE expression is invalid syntax.
• You cannot refer to a column alias inside the sub-query where it is defined (except in an ORDER BY clause) so you cannot use total inside the CASE expression.
• A CASE expression needs to have the same data type in all of its outputs so you cannot mix numbers and strings.
• You GROUP BY PH.PAYMENT_DATE but you do not have any aggregation function around the CASE expression.
• If you do aggregate then 'CURRENCY NOT FOUND' is not something you can total.
• In Oracle, AS before a table alias is invalid syntax.
• You have a mix of quoted identifiers HR."from_ccy" and unquoted identifiers HR.from_ccy. While it is possible to have both in a table, one of them is almost certainly wrong (and using quoted identifiers is bad practice) and your DDL statements do not use quotes.
• Unless you really do intend to get rows from December of any year, do not compare only months. Compare on a range from the start of the month 3-months ago until before the start of the month two-months ago.
• Your PL/SQL block does not have an END statement.
• You are using a SELECT statement in PL/SQL without using SELECT ... INTO.
• You probably don't want to use PL/SQL.
• None of your sample data is from 3 months ago (December 2021).

Something like this:

select PH.PAYMENT_DATE, 
       SUM(
         CASE 
         WHEN PH.CURRENCY = 'GBP'
         THEN PH.AMOUNT
         WHEN PH.CURRENCY IN ('USD', 'AUD', 'EUR')
         AND  HR.from_ccy = PH.CURRENCY
         AND  HR.to_ccy = 'GBP'
         THEN PH.AMOUNT*HR.rate
         END
       ) As total
FROM   historics_rates HR
       RIGHT JOIN PAYMENTS_HISTORY PH
       on     HR.t_date = PH.payment_date
          AND PH.Currency = HR.from_ccy
WHERE  PH.payment_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -3)
AND    PH.payment_date <  ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2)
GROUP BY PH.PAYMENT_DATE;

Which, for the sample data:

CREATE TABLE payments_history(payment_date , account_id , currency, amount) AS
SELECT TO_DATE ('05/12/2021','DD/MM/YYYY'), 2291969088, 'GBP', 10.00  FROM DUAL UNION ALL
SELECT TO_DATE ('05/12/2021','DD/MM/YYYY'), 7851880663, 'USD', 20  FROM DUAL UNION ALL
SELECT TO_DATE ('06/12/2021','DD/MM/YYYY'), 5326844767, 'USD', 3.000  FROM DUAL UNION ALL
SELECT TO_DATE ('05/12/2021','DD/MM/YYYY'), 3668657617, 'EUR', 40  FROM DUAL UNION ALL
SELECT TO_DATE ('06/12/2021','DD/MM/YYYY'), 9040142052, 'GBP', 30.000  FROM DUAL;

Create TABLE Historics_rates(t_date,from_ccy,to_ccy,rate) AS
SELECT  TO_DATE ('06/12/2021','DD/MM/YYYY'),'GBP','EUR',1.1832 FROM DUAL UNION ALL
SELECT  TO_DATE ('06/12/2021','DD/MM/YYYY'),'AUD','GBP',0.5263 FROM DUAL UNION ALL
SELECT  TO_DATE ('06/12/2021','DD/MM/YYYY'),'EUR','GBP',0.8452 FROM DUAL UNION ALL
SELECT  TO_DATE ('05/12/2021','DD/MM/YYYY'),'USD','GBP',0.7388 FROM DUAL UNION ALL
SELECT  TO_DATE ('05/12/2021','DD/MM/YYYY'),'EUR','USD',1.1441 FROM DUAL;

Outputs:

PAYMENT_DATE	TOTAL
06-DEC-21	30
05-DEC-21	24.776

db<>fiddle here