i m trying following query execute gives me warning null
value eliminated aggregate or other set
operation.
select s.currentdate, s.game_id, s.searchstring, sm.type_of_game, case when(count(t.game_id)>0) 'y' else 'n' end scorestatus, case when(count(i.game_id)>0) 'y' else 'n' end scorestatusindividual, sm.image_path,g.datetime,'' score_keeper dbo.search s with(nolock) left outer join dbo.teamgameresult t with(nolock) on s.game_id = t.game_id left outer join dbo.individualgameresult with(nolock) on i.game_id = s.game_id join dbo.game g with(nolock) on g.game_id=s.game_id join dbo.additionaldetails ad on ad.additionaldetails_id = g.additionaldetails_id join dbo.sportmaster sm with(nolock) on sm.sportsmaster_id = ad.sportsmaster_id (( searchstring+' '+ convert(nvarchar(500),s.currentdate,101) '%01/15/2013%' , searchstring+' '+ convert(nvarchar(500),s.currentdate,101) '%soccer%')) , convert(varchar(10),g.datetime,101) in ('01/15/2013','01/14/2013') group s.game_id, s.searchstring, sm.type_of_game, s.currentdate, sm.image_path, g.datetime order g.datetime desc
it warns because using
... when(count(i.game_id)>0) 'y' else 'n' end ...
count aggregate function , can't counts values game_id null. may use isnull function (http://msdn.microsoft.com/en-gb/library/ms184325.aspx) example:
count(isnull(i.game_id, 0))
but calculates null values too. if not want need add additional check section
Comments
Post a Comment