Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Sybase SQL Syntax

Hey guys,

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

Open in new window


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?
0
triphen
Asked:
triphen
1 Solution
 
Kelvin SparksCommented:
Have you tried ISNULL("(select sum(costeach*quan) from dba.posdetail where prodtype in (0,1) and opendate =  20130628  and posdetail.transact = posheader.transact group by posheader.transact),0) as ItemTotal
?

Kelvin
0
 
triphenAuthor Commented:
You rock!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now