December 10, 2004 at 5:22 am
OK, im fairly new to SQL and have really been thrown in at the deep end. I have a DTS package used to import data to table, I then need to run an update SQL query in order to update certain values in the table dependent upon values stored in another table. The update i am using to do this is as follows (its very messy, but it does what i need it to do):
UPDATE [CAF]
SET [Euro_Value] =
(SELECT [Value] FROM [CAF]
WHERE ([Market_ID] = '*') AND ([Cost_Code] = '†') AND (Euro_Value IS NULL )) /
(SELECT [ExRate] FROM [CURRENCY]
WHERE ([Currency_ID] = '*'))
WHERE (Market_ID = '*') AND (Cost_Code = '†') AND (Euro_Value IS NULL )
Basically, i need to know if i can populate the * and † dynamically using DTSGlobal variables maybe? or if anyone has any other suggestion that i may be able to try, i am open to them.
The reason i want to dynamically populate this is because if i dont, i will end up with over 180 of these statements because of all the varying conditions.
If you need to know anything else, just ask and ill supply the information.
Thanks in advance,
Connor
December 10, 2004 at 7:38 am
does the table CAF have a column called Currency_ID?
Do you want to find the corresponding Rate in the Currency table, based on the Currency_ID, and assign the Euro_Value column to be the Value column * the exchange rate?
(saying what you are tyring to do in regular english, as opposed to showing broken code that doesn't work, will get you answers much faster. sample data and expected results help, too)
if so, is this what you want? :
UPDATE
[CAF]
SET [Euro_Value] = [Value] /
(SELECT [ExRate]
FROM [CURRENCY]
WHERE (CURRENCY.[Currency_ID] = CAF.CURRENCY_ID))
WHERE
([Market_ID] = '*') AND
([Cost_Code] = '†') AND
(Euro_Value IS NULL )
Look at it carefully. there is no need to do a subselect on the columns from the table you are updating -- and, if you do, and don't specific a join condition, you will get data from different rows anyway.
Logically think about and then state what you are trying to do (to yourself or to us), step through the UPDATe statement I gave, use BOL to help you find out what is happening, test it with some sample data, and then let us know the results.
January 12, 2005 at 3:24 am
Sorry it took so long to get back to you on this, and even sorrier that i didnt explain my self too well, but yes, your explanation of "Do you want to find the corresponding Rate in the Currency table, based on the Currency_ID, and assign the Euro_Value column to be the Value column * the exchange rate? " Is correct.
But i'm still stuck on the same problem of whether or not you can poplulate sql WHERE clauses dynamically in DTS?
C
January 12, 2005 at 3:46 am
Sorry Yoda, I posted that last post in haste, before i ahd actually just sat down, looked at your code and thought about the problem.
I have added a small sub select query to select the Market_ID's from a different table and, without even needing to use Cost_code, got it all working fine. The code is:
UPDATE CAF
SET Euro_Value = Value /
(SELECT [ExRate] FROM [CURRENCY]
WHERE (CURRENCY.[Currency_ID] = CAF.Market_ID))
WHERE (Market_ID IN
(SELECT Market_ID FROM Market)) AND (Euro_Value IS NULL)
Works fine, thanks for your original post and sorting out my messy original post (",)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply