Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tryharder
Starting Member
17 Posts |
 Posted - 2014-11-26 : 09:17:14
|
| Hi,I'm running a pre-defined script which used to work fine on a file which was resupplied every month. below is the script used and the error message. looking at the error I assume that there is a rouge record within the file but have looked in Textpad and cannot find it. appreciate your help. UPDATE [matching].[dbo].[hot_nov] SET [AOV] = (CAST([Demand] AS DECIMAL)/CAST([Orders] AS INT)) WHERE [Demand] <> '';UPDATE [matching].[dbo].[hot_nov] SET [POST2] = left([PostCode], PATINDEX('%[0-9]%', [PostCode] + '1') - 1) ;UPDATE [matching].[dbo].[hot_nov] SET [POST4] = left([PostCode],LEN([PostCode])-4);UPDATE [matching].[dbo].[hot_nov] SET [POST6] = left([PostCode],LEN([PostCode])-2);error message(1000 row(s) affected)Msg 245, Level 16, State 1, Line 181Conversion failed when converting the varchar value '2014-09-03 00:00:00' to data type int.SP |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-26 : 09:35:44
|
"rouge" or "rogue"? :-)I'm guessing that column Orders has a date in it for some row. try this:select * from matching.dbo.hot_nov where orders = '2014-09-03 00:00:00' do you get any hits? |
 |
|
|
tryharder
Starting Member
17 Posts |
Posted - 2014-11-26 : 09:46:56
|
sorry "rogue" ran query and no hits?SP |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-26 : 10:26:50
|
| What are the datatypes of the columns AOV, POST2, POST4 and POST6? |
|
|
|
tryharder
Starting Member
17 Posts |
Posted - 2014-11-26 : 11:03:14
|
| AOV is the average order value we get this by dividing the number of transactions by the total spend. the post2,4,6 is just a breakdown of the postcode in to 2,4,6 bytereally all I need to do is as described above just divide the number of orders by the total spend which is called "demand" in my file.as I said the query use to work and now doesn't if you have another way to achieve this would be most helpful?SP |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-26 : 11:19:53
|
| Those are not datatypes. Those are business descriptions, What are the datatypes? |
|
|
|
|
|
|
|
|
Error