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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: