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.
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.