August 6, 2008 at 10:19 am
The solution you gave me worked when I embeded the getdate() in single quotes in my string. problem now is when I want to use the dynamically created columns to run updates I get conversion errors. When I hard code it works ..Where am going wrong ? I have attached sample data and the script that I''m using to generate my table...
I have this table which I create dynamically. What I want to do is to
run an update on the table using the columns I have generated.
--==== If I run it this way it works because I copy the column names and hard code them
UPDATE EDW_STAGE..tbComm_3Months_Ago
SET Missed_Instalment = ((Jul08AMT + Jun08AMT + May08AMT /
(3 * ISNULL(CONVERT(DECIMAL(18,3),EvenInstalment),0))))
--- Tried this .............out of desperation and it gives me this error.
/**
Msg 8114, Level 16, State 5, Line 25
Error converting data type varchar to numeric.
**/
DECLARE @Col1 VARCHAR(10),@Col2 VARCHAR(10),@Col3 VARCHAR(10)
SET @Col1 = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbComm_3Months_Ago'
AND ORDINAL_POSITION =(6))
SET @Col2 = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbComm_3Months_Ago'
AND ORDINAL_POSITION =(7))
SET @Col3 = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbComm_3Months_Ago'
AND ORDINAL_POSITION =(8))
--SELECT @Col1,@Col2,@Col3
--?????????????????? gives me an error
UPDATE EDW_STAGE..tbComm_3Months_Ago
SET Missed_Instalment = ((ISNULL(CONVERT(DECIMAL(18,3),@Col1),0) +
ISNULL(CONVERT(DECIMAL(18,3),@Col2),0) +
ISNULL(CONVERT(DECIMAL(18,3),@Col3),0)/
(3 * ISNULL(CONVERT(DECIMAL(18,3),EvenInstalment),0))))
Msg 8114, Level 16, State 5, Line 25
Error converting data type varchar to numeric.
August 6, 2008 at 10:41 am
This is what you are probably trying to do:
declare @SQLUpdateCmd varchar(max);
set @SQLUpdateCmd = 'UPDATE EDW_STAGE..tbComm_3Months_Ago SET ' +
'Missed_Instalment = ((ISNULL(CONVERT(DECIMAL(18,3),' + @Col1 + '),0) + ' +
'ISNULL(CONVERT(DECIMAL(18,3),' + @Col2 + '),0) + ' +
'ISNULL(CONVERT(DECIMAL(18,3),' + @Col3 + '),0)/ ' +
'(3 * ISNULL(CONVERT(DECIMAL(18,3),EvenInstalment),0))));'
exec (@SQLUpdateCmd);
Please NOTE, you will get a divide by zero error if EvenInstalment is null!
Edit: Or if it is zero.
😎
August 6, 2008 at 10:51 am
Hi raym,
Just got you internal message now.
I would also go with Lynn's answer on this one.
Sorry for the delay 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply