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 |
|
snufse
Constraint Violating Yak Guru
469 Posts |
 Posted - 2010-05-28 : 16:56:38
|
Have following line:coalesce(substring(dbo.Employee.Name, 1, 12), '**Unknown**') as employee_name When I run my query I get error on above (if I remove above line from the query it works fine). I do not really understand what the 'coalesce' does, wonder if I can use isnull? Thank you.Warning: Null value is eliminated by an aggregate or other SET operation.(306 row(s) affected)Server: Msg 8152, Level 16, State 2, Line 103String or binary data would be truncated.The statement has been terminated.(0 row(s) affected)(306 row(s) affected) |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-05-28 : 20:49:39
|
| I am guessing that you're trying to insert data into a table and that the column is something like varchar(10) and you're trying to put an 11 or 12 char string into it. Coalesce just returns the first non null thing it finds. It's the same as isnull when there are only tow things.e.g., coalesce(col1,col2) is the same as isnull(col1,col2), but with coalesce you can add more columns like coalesce(col1,col2,col3,...coln) and it will return the first non null value it finds. The warning is nothing to worry about, the error you have to fix, of course. It's basically saying the value is too big to fit in to where you're trying to put it.JimEveryday I learn something that somebody else already knew |
 |
|
|
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2010-06-01 : 08:29:13
|
| Jim, Thank you, the issue was the data base field being 10 characters. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-01 : 09:32:06
|
| "It [COALESCE] is the same as isnull when there are only two things"Except that IsNull() is brain-dead on implicit casting ... another good reason to use COALESCE |
|
|
|
|
|
|
|
|
[Resolved] Null value is eliminated by aggregate