I am trying to get the exchange rate from the Erp.CurrExRate table to be used in a BAQ against a Date01 field in a UD table.
Annoyingly, there is not an ‘EffectiveFrom’ and ‘EffectiveToDate’ to identify the correct recod, rather an effective date that returns all rates prior to the value in the Date01 field.
The solution is pretty straightforward in SQL (see below), but I can’t seem to get this to work using the BAQ editor.
Effectively, its a inner subquery, with it’s own inner subquery, and the nested inner subquery needs to reference a date in the top level query/
I’d really rather not set up an external BAQ, but at the moment I can’t see any other way
Has anyone managed to to do this?
(select rt.currentrate from erp.currexrate rt where effectivedate = (select max(effectivedate) from erp.currexrate rt2 where rt2.effectivedate <= ud.date01 and rt2.sourcecurrcode = ‘GBP’
and rt2.targetcurrcode = ‘USD’) and rt.sourcecurrcode = ‘GBP’
and rt.targetcurrcode = ‘USD’ and rt.company = ‘XXX’) rate
from ice.ud07 ud
where ud.key1 = ‘34219’
So I’m trying to understand what you are trying to do. (I don’t use any exchange rate stuff)
Tell me if this is the basic gist of it:
You have a date and you need to get the record where that date is in the range of effective dates for a table that only has effective from dates. Does that sound correct?
I think I would use this technique to get the specific record that I needed, and then if you need more than one, either duplicate the sub query for each field, or rejoin the table using the one field that you pulled. You can make your subquery joins be > and then return the top1 for that subquery.