[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sybase SQL Syntax

Posted on 2014-03-05
2
Medium Priority
?
313 Views
Last Modified: 2014-03-05
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
Comment
Question by:triphen
2 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 2000 total points
ID: 39908247
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
 

Author Closing Comment

by:triphen
ID: 39908260
You rock!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question