Problem with the Following Cursor

[vuyiswamb]

Good Afternoon All

I have the Following Cursor

DECLARE VAL_CURSOR CURSOR FOR
SELECT PROPERTY_ID, NEW_MARKET_VALUE, NEW_ATTRIB_CODE, 
ACTUAL_EXTENT, GETDATE() AS STATUS_DATE,11 AS VAL_REASON_ID, 6 AS VAL_METHOD_ID
FROM  PROPERTY_MASS
WHERE NEW =1 
 
 
OPEN VAL_CURSOR

DECLARE @PROPERTY_ID INT 
DECLARE @MARKET_VALUE INT
DECLARE @NEW_ATTRIB_CODE VARCHAR(12)
DECLARE @STATUS_DATE DATETIME
DECLARE @NEW_EXTENT FLOAT 
DECLARE @VAL_REASON_ID INT
DECLARE @PAR_METHOD_ID INT

FETCH NEXT FROM VAL_CURSOR INTO
@PROPERTY_ID,
@MARKET_VALUE,
@NEW_ATTRIB_CODE,
@STATUS_DATE,
@NEW_EXTENT,
@VAL_REASON_ID,
@PAR_METHOD_ID

 
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT VALUATION (PROPERTY_ID,NEW_IMPROVED_VALUE,NEW_ATTRIB_CODE,STATUS_DATE,NEW_EXTENT,VAL_REASON_ID,PAR_METHOD_ID)
VALUES(@PROPERTY_ID, @MARKET_VALUE,@NEW_ATTRIB_CODE,@STATUS_DATE,@NEW_EXTENT,@VAL_REASON_ID,@PAR_METHOD_ID)
 
FETCH NEXT FROM VAL_CURSOR INTO
@PROPERTY_ID, @MARKET_VALUE,@NEW_ATTRIB_CODE,@STATUS_DATE,@NEW_EXTENT,@VAL_REASON_ID,@PAR_METHOD_ID
END
 
CLOSE VAL_CURSOR
DEALLOCATE VAL_CURSOR

when i run it gives me the Following Error

Msg 16922, Level 16, State 1, Line 26
Cursor Fetch: Implicit conversion from data type datetime to float is not allowed.
.

What is Wrong

Thanks

[szlamany]

Put this in a query window and see what prints

DECLARE VAL_CURSOR CURSOR FOR
SELECT PROPERTY_ID, NEW_MARKET_VALUE, NEW_ATTRIB_CODE, 
ACTUAL_EXTENT,11 AS VAL_REASON_ID, 6 AS VAL_METHOD_ID
FROM  PROPERTY_MASS
WHERE NEW =1 

OPEN VAL_CURSOR

DECLARE @PROPERTY_ID INT 
DECLARE @MARKET_VALUE INT
DECLARE @NEW_ATTRIB_CODE VARCHAR(12)
DECLARE @STATUS_DATE VARCHAR(100)  -- Note: change datatype here
DECLARE @ACTUAL_EXTENT FLOAT 
DECLARE @VAL_REASON_ID INT
DECLARE @PAR_METHOD_ID INT

SET @STATUS_DATE = GETDATE()

FETCH NEXT FROM VAL_CURSOR INTO
@PROPERTY_ID, @MARKET_VALUE,@NEW_ATTRIB_CODE,@STATUS_DATE,@VAL_REASON_ID,@PAR_METHOD_ID

 
WHILE @@FETCH_STATUS = 0
BEGIN

IF ISDATE(@STATUS_DATE)=0 PRINT CAST(@PROPERTY_ID AS CHAR(20))+'"'+@STATUS_DATE+'"'

FETCH NEXT FROM VAL_CURSOR INTO
@PROPERTY_ID, @MARKET_VALUE,@NEW_ATTRIB_CODE,@STATUS_DATE,@VAL_REASON_ID,@PAR_METHOD_ID
END
 
CLOSE VAL_CURSOR
DEALLOCATE VAL_CURSOR

btw - why are you using a CURSOR instead of

INSERT INTO VALUATION SELECT PROPERTY_ID...FROM PROPERTY_MASS WHERE...

[vuyiswamb]

Thanks for your Reply

Am Running it from the Query Window and that Error is what it is Complaining about.

and it points to this line

FETCH NEXT FROM VAL_CURSOR INTO

Thanks

[szlamany]

Did you see the change in the datatype that I posted?

[vuyiswamb]

Am Sorry about that,

It Printed me the Following

781652              "17"
781653              "21"
781656              "17"
781658              "17"
781667              "77"
781668              "77"
781671              "90"
781671              "90"
781674              "121"
774032              "154"
774034              "147"
Msg 232, Level 16, State 3, Line 28
Arithmetic overflow error for type int, value = 7153000000.000000.

Thanks

[szlamany]

As you can see from that print out - you have bad values in the date fields.

17 and 21 are not dates.

Bad data - can you trace where it came from?

As for that formatting error - that's another field. You can change one of the INT's to varchar(100) - one at a time - and see exactly which field it is.

This is no way to be scrubbing data - what is the data source?

[vuyiswamb]

Good Evening thanks it worked Fine

i have Adjusted the datatype from the Source table to the Destination Table

thanks you are a Star