return default value '0' to resultset if column is null

Question

Hi

declare  @Date as datetime
select @Date='01/17/2013'
select PD.copies from Pheader PH
inner join Pdetails PD on  PH.id=Pd.PH_id
where convert(char(3),datename (dw,PH.IDate))=convert(char(3),datename (dw,@Date))
and PH.PBid=4 and PD.Ds_id in (2,3)

I have this sqlquery  here i am returning Copies value Null

here how i return default value '0'  to resultset if copies value Null

---

adil

Answer 1

 Use isnull function like this....

declare  @Date as datetime
select @Date='01/17/2013'
select isnull(PD.copies,0) as Copies from Pheader PH
inner join Pdetails PD on  PH.id=Pd.PH_id
where convert(char(3),datename (dw,PH.IDate))=convert(char(3),datename (dw,@Date))
and PH.PBid=4 and PD.Ds_id in (2,3)

look at below link.. for more details about ISNULL

http://msdn.microsoft.com/en-us/library/ms184325.aspx

Thanks,

saurabh

---

http://www.linkedin.com/profile/view?id=36482856&trk=tab_pro http://www.experts-exchange.com/M_6313078.html

Answer 2

Hi

actually here resultset returns nothing

this Means there is no rows in databse  with  matched where condition

with pbid value with 4 and ds_d value 2,3

---

adil

Answer 3

I think you want to show empty row even when the record for the person is not there.

In that case you can use LEFT JOIN instead of INNER JOIN and use condition as:

SELECT (CASE WHEN PD.Copies IS NULL THEN 0 ELSE PD.Copies END)

Answer 4

How i use isnull function in  this subquery

acutally i am using this query in subquery

declare  @Date as datetime
select @Date='01/17/2013'
SELECT 2 AS INo, @Date AS IDate, vw.DCode, vw.DName,
vw.Pages, (
select isnull(PD.Pcopies,0) as 'PD_COPIES' from PHeader PH
inner join PDetails PD on  PH.Po_id=Pd.PD_Po_id
where convert(char(3),datename (dw,PH.PO_IDate))=convert(char(3),datename (dw,@Date))
and PH.po_pb_id=4 and PD.PD_Ds_id in (DS_ID)
) AS Copies
FROM vwGetPOdest vw
where vw.PB_ID=4 and vw.PT_ID=2

---

adil

Answer 5

have a look at ISNULL function is SQL, 

ISNULL(Value, RETURN_VALUE_WHEN_NULL)

Regards
satheesh

Answer 6

Hi Adil,

I guess this is your answer.

DECLARE @Date AS DATETIME;
SELECT  @Date = '01/17/2013';

SELECT  ISNULL(PD.copies, 0)
FROM    ( VALUES ( 4, 2 )
			   , ( 4, 3 ) ) AS T( PBid, Ds_id )
	LEFT OUTER JOIN Pheader PH 
		ON t.PBid = PH.PBid
    LEFT OUTER JOIN Pdetails PD 
		ON PH.id = Pd.PH_id 
		AND t.Ds_id = PD.Ds_id
WHERE   CONVERT(CHAR(3), DATENAME(dw, PH.IDate)) = CONVERT(CHAR(3), DATENAME(dw, @Date))
        AND PH.PBid = 4
        AND PD.Ds_id IN ( 2, 3 );

Regards

Saeid

---

http://sqldevelop.wordpress.com/

Answer 7

in case you need to return a default row, without using LEFT JOINS -

declare  @Date as datetime
select @Date='01/17/2013'
select PD.copies from Pheader PH
inner join Pdetails PD on  PH.id=Pd.PH_id
where convert(char(3),datename (dw,PH.IDate))=convert(char(3),datename (dw,@Date))
and PH.PBid=4 and PD.Ds_id in (2,3)
UNION
SELECT 0 AS copies

Answer 8

You need to decide what you want to return: a scalar or a result set?

Currently, your query will return a set of rows. If there are no rows that satisfy the criteria, an empty set is returned (a set with no rows).

If this query will return at most one row, then you could consider rewriting it to return a scalar. For example:

declare  @Date as datetime
select @Date='01/17/2013'

declare @return int
set @return = ISNULL((
  select PD.copies
  from Pheader PHinner
  join Pdetails PD
    on  PH.id=Pd.PH_id
  where convert(char(3),datename (dw,PH.IDate))=convert(char(3),datename (dw,@Date))
  and PH.PBid=4
  and PD.Ds_id in (2,3)
),0)

-- return @return

---

Gert-Jan

Answer 9

Try

declare  @Date as datetime
select @Date='20130117'

declare @t table (Copies int)
insert into @t (Copies)
select PD.copies from Pheader PH
inner join Pdetails PD on  PH.id=Pd.PH_id
where convert(char(3),datename (dw,PH.IDate))=convert(char(3),datename (dw,@Date)) -- are you looking for same day of the week?
and PH.PBid=4 and PD.Ds_id in (2,3)

IF @@ROWCOUNT = 0
  select 0 as Copies
ELSE
   select * from @t

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog