how to set value 0 or zero as a Default for the Maximum query [Resolved]

Posted by Vanchi050585 under Sql Server on 8/28/2010 | Points: 10 | Views : 986 | Status : [Member] | Replies : 2
hi all,

I wrote max query like this,
select Max(Empsalary) as salry from table1 where EmpID='1000'

but if the records not available that time empty will be displayed. so i need value zero in that.

Thanks

Thanks
tvn



Responses

Posted by: PandianS on: 8/28/2010 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi,

You have two scenario:

1. Record is there for EmpID = '1000', But SUM of the result is NULL then
Select ISNULL(Max(Empsalary),0) as salry from table1 where EmpID='1000' 

2. Record also not there for EmpID = '1000' then
IF EXISTS(Select 1 from table1 where EmpID='1000') 

Select ISNULL(Max(Empsalary),0) as salry from table1 where EmpID='1000'
ELSE
Select 0 as salry

Cheers




Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Vanchi050585, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 8/29/2010 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
use this

select isNull( Max(Empsalary),0) as salry from table1 where EmpID='1000'


Thank you for posting at Dotnetfunda
[Administrator]

Vanchi050585, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response