Solved

Sybase SQL Syntax

Posted on 2014-03-05
2
282 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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Peformance + mulitple query plans 9 55
Oracle 10g - Select rows into colums 4 21
Access 2016 - query 23 59
Email Header Detail 12 54
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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