triphen
asked on
Sybase SQL Syntax
Hey guys,
Using SyBase SQL Anywhere v10.
On line 5 where is says "(select sum(costeach*quan) from dba.posdetail where prodtype in (0,1) and opendate = 20130628 and posdetail.transact = posheader.transact group by posheader.transact) as ItemTotal" somestimes it returns a (null) value because there are no items that match "prodtype in (0,1)" to sum. How can I have it returns a zero (0) instead of null?
Using SyBase SQL Anywhere v10.
select
POSHEADER.transact, right(replicate('0',4) + convert(varchar,POSHEADER.snum),4), POSHEADER.statnum, whoclose, convert(varchar, timeend ,120),
case when POSHEADER.finaltotal > 0 then 'Sale' else 'Return' end as Type,
case when POSHEADER.status = 3 then 'Successful' else 'Failed' end as Status,
METHODPAY.descript, (select sum(costeach*quan) from dba.posdetail where prodtype in (0,1) and opendate = 20130628 and posdetail.transact = posheader.transact group by posheader.transact) as ItemTotal , finaltotal, tax1, whoclose
from dba.POSHEADER, dba.HOWPAID, dba.METHODPAY
where POSHEADER.transact = HOWPAID.transact and
HOWPAID.methodnum = METHODPAY.methodnum and
HOWPAID.approved = 1 and
POSHEADER.opendate = 20130628 and
POSHEADER.transact not in (Select transact from dba.posheader where transact in (Select transact from dba.posdetail Group by transact having Count(*) = 1) and opendate = 20130628 )
order by POSHEADER.transact
On line 5 where is says "(select sum(costeach*quan) from dba.posdetail where prodtype in (0,1) and opendate = 20130628 and posdetail.transact = posheader.transact group by posheader.transact) as ItemTotal" somestimes it returns a (null) value because there are no items that match "prodtype in (0,1)" to sum. How can I have it returns a zero (0) instead of null?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER