Hi All,
I have had major success with help on this site and, with my project nearing an end, im back for one last question (I hope). I have several packages up and running now that import data and do weird and wonderful things to the data. In order to make life easier for the poor soul that comes after me to maintain and use these packages i was wondering if it was possible to create dynamic SQL where by a few 'where' clauses change every time a loop runs. This is my scenario:
I am importing files to a CAF table that contain money values. I need to convert these different values into one currency - Euros. The two tables I have at the moment are as follows:
CAF: Warehouse_ID, Market_ID, Year, Month, Cost_Code, Value, Euros.
When the file is imported, Euro's is a null value as this will later be set using a SQL task. The 'Value' field is used to change the 'Euros' field based on the following table which holds the exchange rates:
CURRENCY: Currency_ID, ExRate
Currency_ID holds values that are in Market_ID. The SQL code I am currently using to update my CAF table is as follows:
UPDATE CAF
SET Euro_Value = (SELECT [Value] FROM [CAF] WHERE ([Market_ID] = 'AUS') AND ([Cost_Code] = 'W101') AND (Euro_Value IS NULL)) /(SELECT [ExRate] FROM [CURRENCY]
WHERE ([Currency_ID] = 'AUS'))
WHERE (Market_ID = 'AUS') AND (Cost_Code = 'W101') AND (Euro_Value IS NULL)
This code does work, all be it that it is messy. This is where the problem comes into it - there are currently twenty markets and 10 cost codes - this means that this code is replicated over 200 times - which is just crazy!!! I would like to know if there is any way that I can replace Market_ID and Cost_Code dynamically?
Also, note that changes to these two table designs are still possible as this will only impact on this part of the database because no other tables/packages are using them.
As per usual, any and all suggestions are welcome and if you would like to know anymore about the problem, just let me know.
I have had major success with help on this site and, with my project nearing an end, im back for one last question (I hope). I have several packages up and running now that import data and do weird and wonderful things to the data. In order to make life easier for the poor soul that comes after me to maintain and use these packages i was wondering if it was possible to create dynamic SQL where by a few 'where' clauses change every time a loop runs. This is my scenario:
I am importing files to a CAF table that contain money values. I need to convert these different values into one currency - Euros. The two tables I have at the moment are as follows:
CAF: Warehouse_ID, Market_ID, Year, Month, Cost_Code, Value, Euros.
When the file is imported, Euro's is a null value as this will later be set using a SQL task. The 'Value' field is used to change the 'Euros' field based on the following table which holds the exchange rates:
CURRENCY: Currency_ID, ExRate
Currency_ID holds values that are in Market_ID. The SQL code I am currently using to update my CAF table is as follows:
UPDATE CAF
SET Euro_Value = (SELECT [Value] FROM [CAF] WHERE ([Market_ID] = 'AUS') AND ([Cost_Code] = 'W101') AND (Euro_Value IS NULL)) /(SELECT [ExRate] FROM [CURRENCY]
WHERE ([Currency_ID] = 'AUS'))
WHERE (Market_ID = 'AUS') AND (Cost_Code = 'W101') AND (Euro_Value IS NULL)
This code does work, all be it that it is messy. This is where the problem comes into it - there are currently twenty markets and 10 cost codes - this means that this code is replicated over 200 times - which is just crazy!!! I would like to know if there is any way that I can replace Market_ID and Cost_Code dynamically?
Also, note that changes to these two table designs are still possible as this will only impact on this part of the database because no other tables/packages are using them.
As per usual, any and all suggestions are welcome and if you would like to know anymore about the problem, just let me know.