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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-09-10 : 09:36:47
|
| Bill writes "Using SQL Server 2000 and the following query is returning NULL's for the final_amount_var, po_amount_var, labor_total_var variables instead of 0 (zero). Do you know what could be causing this problem? SELECT a.project_num AS Project_Number, c.Project_City, c.Owner, c.Target_Date,a.total_approved, e.STATUS_DESCRIPTION, f.LNAME, f.FNAME,(select SUM(CASE WHEN final_amount IS NULL THEN 0 ELSE final_amount END) from pur_req where project_num = a.project_num and afe_num = '036546') AS final_amount_var,(select SUM(CASE WHEN po_amount = 0 THEN 0 ELSE po_amount END) from pur_req where project_num = a.project_num and afe_num = '036546' and final_amount = 0.00) AS po_amount_var,(select SUM(CASE WHEN labor_total = 0 THEN 0 ELSE labor_total END) from internal_labor where project_num = a.project_num and afe_num = '036546') AS labor_total_varFROM funding_request a, Project_Info c, activity_log d, Project_Status_Types e, User_Info fWHERE a.project_num = c.project_num anda.project_num = d.project_num andc.project_status = e.project_status andd.pm = f.user_id and a.afe_number = '036546' anda.status <> 'Disapproved'GROUP BY a.project_num, c.Project_City, c.Owner, c.Target_Date, a.total_approved, e.STATUS_DESCRIPTION, f.LNAME, f.FNAME, c.Project_Status" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-10 : 09:47:33
|
| no entries in pur_req where project_num = a.project_num and afe_num = '036546' for final group?Try puttin a coalesce around the whole select.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
Returns Nulls Instead Of 0