August 7, 2008 at 9:29 am
I'm trying to
DECLARE @Records INT
UPDATE STAGE..tbComm_3Months_Ago
SET Miss_Payment_Flag = CASE WHEN Missed_Instalment < 0.3333 THEN 1 ELSE 0 END
SET @Records = ISNULL(@@ROWCOUNT,0)
select @Records gives me 1 row..I want it to return all the rows that have been affected. how do I do that
August 7, 2008 at 9:40 am
just before you do the update, issue:
select * from STAGE..tbComm_3Months_Ago where Missed_Instalment <0.3333
a better question is... you're updating ALL records where missed_instalment is not NULL, so what exactly are you trying to log? i mean, once you're done, any records that are now 1 used to be less than 0.3333... any records that are 0 weren't less than 0.3333.
August 7, 2008 at 9:45 am
This is what you are probably wanting to do:
DECLARE @Records INT
UPDATE STAGE..tbComm_3Months_Ago SET
Miss_Payment_Flag = CASE WHEN Missed_Instalment < 0.3333 THEN 1 ELSE 0 END
SET @Records = @@ROWCOUNT
select isnull(@Records,0)
😎
August 7, 2008 at 10:12 am
ENDED UP RE-WRITING IT
SET @Records = (SELECT COUNT(*) FROM EDW_STAGE..tbComm_3Months_Ago)
August 7, 2008 at 10:19 am
raym (8/7/2008)
ENDED UP RE-WRITING ITSET @Records = (SELECT COUNT(*) FROM EDW_STAGE..tbComm_3Months_Ago)
This is another way to write the code above:
select @Records = count(*) from EDW_STAGE..tbComm_3months_Ago;
August 8, 2008 at 3:15 am
USE EDW_Stage
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))
DECLARE @Records VARCHAR(MAX);
SET @Records = 'SELECT COUNT(*) FROM tbComm_3Months_Ago WHERE
(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))
< 0.3333;'
EXEC @Records
---From clipboard after selecting & pasting query generated
SELECT COUNT(*) FROM tbComm_3Months_Ago
WHERE (ISNULL(CONVERT(DECIMAL(18,3),Jul08AMT),0) +
ISNULL(CONVERT(DECIMAL(18,3),Jun08AMT),0) +
ISNULL(CONVERT(DECIMAL(18,3),May08AMT),0))/
(3 * ISNULL(CONVERT(DECIMAL(18,3),EvenInstalment),0)) < 0.3333;
If select @records and copy the code into query analyser it works. But when I
exec @records I get the following error
Msg 203, Level 16, State 2, Line 25
The name 'SELECT COUNT(*) FROM tbComm_3Months_Ago WHERE
(ISNULL(CONVERT(DECIMAL(18,3),Jul08AMT),0) +
ISNULL(CONVERT(DECIMAL(18,3),Jun08AMT),0) +
ISNULL(CONVERT(DECIMAL(18,3),May08AMT),0))/
(3 * ISNULL(CONVERT(DECIMAL(18,3),EvenInstalment),0)) < 0.3333;' is not a valid identifier.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply