I need to get exchange rate of a currency that wil...
# suiteqls
Selcuk Dogru
04/07/2022, 2:27 PMI need to get exchange rate of a currency that will be used for reporting in a transaction query. Would you think of a better way than below?
and still couldn’t find a way to calculate exchange rate of a 3rd currency. Transaction currency is USD, Subsidiary currency is EUR and Reporting currency is GBP. I need to get USD to EUR / EUR to GBP
Copy code
(SELECT TOP 1 CR.exchangerate
FROM currencyrate CR
WHERE CR.basecurrency = C.id AND
CR.transactioncurrency = 4 AND
CR.effectivedate <= T.trandate
ORDER BY CR.effectivedate DESC
)Selcuk Dogru
04/07/2022, 2:36 PMbelow query works but I can’t embed it to transaction query because I can’t pass T.trandate to the 2nd nested select
Copy code
SELECT (SELECT TOP 1 CR.exchangerate
FROM currencyrate CR
WHERE CR.basecurrency = 1 AND
CR.transactioncurrency = 4 AND
CR.effectivedate <= TO_DATE('20220406', 'YYYYMMDD')
ORDER BY CR.effectivedate DESC) / (SELECT TOP 1 CR.exchangerate
FROM currencyrate CR
WHERE CR.basecurrency = 1 AND
CR.transactioncurrency = 2 AND
CR.effectivedate <= TO_DATE('20220406', 'YYYYMMDD')
ORDER BY CR.effectivedate DESC) AS "Test" FROM dualc
CD
04/10/2022, 7:28 PMUse a CTE
s
Selcuk Dogru
04/12/2022, 7:37 AMI don’t think that will solve my issue. I need to calculate on row level depending on the transaction date. so the date should come as dynamic from results