Solved

Sybase SQL Syntax

Posted on 2014-03-05
2
296 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

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.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

726 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