I have a table for exchange rates. Let me call it the Exchange Rate table.
The fields are month, USD rate, GBP rate, CHF rate. I have another table with customer
name, month and currency type. I need to build a query to look up the exchange rate from
the Exchange Rate table for each currency value in the customer records. My problem is
looking up different fields (variable) when using the SELECT statement. How do I join on
variable field names? Thanks.
Requires Free Membership to View
How do you join on variable field names? Simple—by redesigning your exchange rate table.
Instead of this --
Month USD GBP CHF Jun 2005 1.223 1.937 1.115 Jul 2005 1.210 1.895 1.075 Aug 2005 1.242 2.014 1.228
You should have this --
Month Curr Rate Jun 2005 USD 1.223 Jun 2005 GBP 1.937 Jun 2005 CHF 1.115 Jul 2005 USD 1.210 Jul 2005 GBP 1.895 Jul 2005 CHF 1.075 Aug 2005 USD 1.242 Aug 2005 GBP 2.014 Aug 2005 CHF 1.228
This structure is immensely easier to fit a brand new currency into. With your existing table, you have to add a new column, and furthermore, you will undoubtedly need to modify every query in the application.
With the normalized table design, you can accommodate a new currency easily. But even if you weren't planning to add any new currencies—and can you really say you will never need to do that?—the query to join to the customer records will be a lot easier to write, too. Try it, and let me know if you have any problems.
This was first published in September 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation