I have two tables
x
Payments_historyCREATE TABLE payments_history(payment_date , account_id , currency, amount) ASSELECT TO_DATE ('05/01/2022','DD/MM/YYYY'), 2291969088, 'GBP', 10.00 FROM DUAL UNION ALLSELECT TO_DATE ('05/01/2022','DD/MM/YYYY'), 7851880663, 'USD', 20 FROM DUAL UNION ALLSELECT TO_DATE ('06/01/2022','DD/MM/YYYY'), 5326844767, 'USD', 3.000 FROM DUAL UNION ALLSELECT TO_DATE ('05/01/2022','DD/MM/YYYY'), 3668657617, 'EUR', 40 FROM DUAL UNION ALLSELECT TO_DATE ('06/01/2022','DD/MM/YYYY'), 9040142052, 'GBP', 30.000 FROM DUAL Historics_ratesCreate TABLE Historics_rates(t_date,from_ccy,to_ccy,rate) ASSELECT TO_DATE ('06/01/2022','DD/MM/YYYY'),'GBP','EUR',1.1832 FROM DUAL UNION ALLSELECT TO_DATE ('06/01/2022','DD/MM/YYYY'),'AUD','GBP',0.5263 FROM DUAL UNION ALLSELECT TO_DATE ('06/01/2022','DD/MM/YYYY'),'EUR','GBP',0.8452 FROM DUAL UNION ALLSELECT TO_DATE ('05/01/2022','DD/MM/YYYY'),'USD','GBP',0.7388 FROM DUAL UNION ALLSELECT 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 GBP2022-01-05 24.78 This is what I have done so far
DECLAREtotal NUMBER;BEGINselect 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 totalFROM "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 keywordORA-06550: line 6, column 1:PL/SQL: SQL Statement ignoredORA-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-quotAdvertisement
Answer
Your errors include:
- Using
=inside theTHENclause of aCASEexpression is invalid syntax. - You cannot refer to a column alias inside the sub-query where it is defined (except in an
ORDER BYclause) so you cannot usetotalinside theCASEexpression. - A
CASEexpression needs to have the same data type in all of its outputs so you cannot mix numbers and strings. - You
GROUP BY PH.PAYMENT_DATEbut you do not have any aggregation function around theCASEexpression. - If you do aggregate then
'CURRENCY NOT FOUND'is not something you can total. - In Oracle,
ASbefore a table alias is invalid syntax. - You have a mix of quoted identifiers
HR."from_ccy"and unquoted identifiersHR.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
ENDstatement. - You are using a
SELECTstatement in PL/SQL without usingSELECT ... 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 totalFROM historics_rates HR RIGHT JOIN PAYMENTS_HISTORY PH on HR.t_date = PH.payment_date AND PH.Currency = HR.from_ccyWHERE 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) ASSELECT TO_DATE ('05/12/2021','DD/MM/YYYY'), 2291969088, 'GBP', 10.00 FROM DUAL UNION ALLSELECT TO_DATE ('05/12/2021','DD/MM/YYYY'), 7851880663, 'USD', 20 FROM DUAL UNION ALLSELECT TO_DATE ('06/12/2021','DD/MM/YYYY'), 5326844767, 'USD', 3.000 FROM DUAL UNION ALLSELECT TO_DATE ('05/12/2021','DD/MM/YYYY'), 3668657617, 'EUR', 40 FROM DUAL UNION ALLSELECT 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) ASSELECT TO_DATE ('06/12/2021','DD/MM/YYYY'),'GBP','EUR',1.1832 FROM DUAL UNION ALLSELECT TO_DATE ('06/12/2021','DD/MM/YYYY'),'AUD','GBP',0.5263 FROM DUAL UNION ALLSELECT TO_DATE ('06/12/2021','DD/MM/YYYY'),'EUR','GBP',0.8452 FROM DUAL UNION ALLSELECT TO_DATE ('05/12/2021','DD/MM/YYYY'),'USD','GBP',0.7388 FROM DUAL UNION ALLSELECT 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