BAQ Question - nested inner subquery

Hi,

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?

Thanks,

Andrew.

select ud.date01,
(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.

Let me know if that makes sense.

When I’ve used this table I’ve ended up with a slightly clumsy set of sub-queries that do work.

I get MAX(EffectiveDate) from the matching criteria of CurrExRate grouped by the criteria, with EffectiveDate less than the date I want.

I then join to CurrExRate again using that date to return only the row I want.

Hi Brandon,

Thanks for this - it pointed me in the right direction.

The key was the calculated field based upon the subquery.

Thanks again,

Andrew.

1 Like