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 |
|
allan8964
Posting Yak Master
249 Posts |
 Posted - 2012-12-12 : 10:49:57
|
| Hi there,Plz help me to solve this:table2 needs data inserted from table1 and all rows can't have any null column. table2 feed 20 charts (in reporting service), if there is null then the charts shows No data available. So it's not allowed. But when I select data from table1 not all records return.table2Name | Date | Total |john | ... | 23 |Smith| ... | 14 |Kim | ... | NULL |....table1 Name | ... | Num1 | Num2 | ....john | ... | 213 | 908 |Smith| ... | 314 | 732 | ........insert into table2(Name, Date, Total) select Name, Date, Sum(Num1)+Sum(Num2)/2where .....Each time select statement can't guaranty to get a record because of the WHERE clause. So the Name and Data inserted but Total values are null. If I set Total not null in table2 I got error in execution. I tried usgin default, seems not working. How can I solve this? Thanks in advance. |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-12-12 : 11:13:29
|
| SELECT Name,Date, COALESCE(Sum(Num1)+Sum(Num2)/2,0)where ...If this does not solve your problem, please follow the link in my signature and provide the information it asks for.How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-12-12 : 16:49:22
|
| thanks, it improves but still has null ... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-12 : 20:51:33
|
| [code]SELECT Name,Date, (Sum(isnull(Num1,0))+Sum(isnull(Num2,0)))/2where ...[/code] |
|
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-12-13 : 10:46:36
|
| Thanks guys!I noticed that the reason for null in table2 is the select statement gets nothing to insert from table1 because of the where conditions. If table1 selects nothing then the table2 got all Num values null but other column like Date, Name are filled. If this is the case, at least I need make the Num column filled with 0.returns:Name | Date | Num | ...John | 11/2 | NULL| ...ideal:Name | Date | Num | ...John | 11/2 | 0 | ...Thanks again. |
|
|
|
|
|
|
|
|
how to avoid null?