Q

Joining on variable field names

I have a table for exchange rates. I need to build a query to look up the exchange rate from the 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?

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.

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close