Re: Help with join syntax sought

James B. Byrne wrote:
> I have a requirement to select the effective exchange rate for a
> number of currencies as of a specific date and time. The rates may
> come from several sources for the same currency. For some
> currencies the rate may be set infrequently. I have come close to
> getting this to work but cannot seem to get the last bit figured
> out. Thus my appeal for help.
>
> The table currency_exchange_rates has a composite unique index made
> up of:
>
> fxr.currency_code_base
> fxr.currency_code_quote
> fxr.effective_from
> fxr.currency_exchange_type
>
> Here is what I have so far:
>
> SELECT
> fxr.currency_code_base AS fx_base,
> fxr.currency_code_quote AS fx_quote,
> fxr.effective_from AS fx_date,
> fxr.currency_exchange_type AS fx_type,
> fxr.currency_exchange_rate AS fx_rate
>
> FROM
> currency_exchange_rates AS fxr
>
> LEFT OUTER JOIN
> currency_exchange_rates AS fxr_j
>
> ON
> fxr.currency_code_base = fxr_j.currency_code_base
> AND
> fxr.currency_code_quote = fxr_j.currency_code_quote
> AND
> fxr.currency_exchange_type = fxr_j.currency_exchange_type
> AND
> fxr.effective_from >= fxr_j.effective_from
>
> WHERE
> fxr.currency_code_base = 'CAD'
> AND
> fxr.effective_from <= current_timestamp
>
> GROUP BY
> fx_base,
> fxr.currency_code_quote,
> fx_date,
> fxr.currency_exchange_type,
> fx_rate
>
> HAVING
> COUNT(fxr.currency_code_quote) = 1
>
> ORDER BY
> fx_base,
> fxr.currency_code_quote,
> fx_date DESC
>
>
> My problem with this version is that currencies with rates from more
> than one type show up for each type. This I do not want. I want
> only the most recent regardless of type. However, I need to display
> the type in the final report.
>
> Further, if I take the fxr.currency_exchange_type out of the SELECT
> columns, making the appropriate adjustments elsewhere, then all
> those currencies with more than one type are not selected at all.
>
> I am perplexed why I cannot select a column from the table without
> having to include it in the GROUP BY clause as well.
>
> Any help is welcomed.
>

If your query above is getting you mostly what you want, just use it as
a derived table.

so:

select subtable.fx_base, etc from (

> SELECT
> fxr.currency_code_base AS fx_base,
> fxr.currency_code_quote AS fx_quote,
> fxr.effective_from AS fx_date,
> fxr.currency_exchange_type AS fx_type,
> fxr.currency_exchange_rate AS fx_rate
>
> FROM
> currency_exchange_rates AS fxr
>
> LEFT OUTER JOIN
> currency_exchange_rates AS fxr_j
>
> ON
> fxr.currency_code_base = fxr_j.currency_code_base
> AND
> fxr.currency_code_quote = fxr_j.currency_code_quote
> AND
> fxr.currency_exchange_type = fxr_j.currency_exchange_type
> AND
> fxr.effective_from >= fxr_j.effective_from
>
> WHERE
> fxr.currency_code_base = 'CAD'
> AND
> fxr.effective_from <= current_timestamp
>
> GROUP BY
> fx_base,
> fxr.currency_code_quote,
> fx_date,
> fxr.currency_exchange_type,
> fx_rate
>
> HAVING
> COUNT(fxr.currency_code_quote) = 1
>
> ORDER BY
> fx_base,
> fxr.currency_code_quote,
> fx_date DESC

) as subtable