Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
.NET
TIA
__StephenTry
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:

> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>

Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
.NET
TIA
__StephenTry
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:
> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>

Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
..NET
TIA
__Stephen
Try
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>
|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:

> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>

Retrieve Date using input parameters w/o GUI

HI
I want to retrieve data in between two date formats using a query in SQL?
can i do it w/o using GUI tools?
For Exp i have sales data from date 11/11/2000 to 11/2004.
now as a user i want to give Input paramete value ranging between 06/06/2002 and 07/07/2002?
Is there any SQL query which i can use to retrieve the above date values?
Thanx in Advance
VSselect foo, bar
from salestable
where salesdate between '2002-06-06' and '2002-07-07'|||I am sorry i could not understand what is foo bar>
my ? was is there any chance of retieving data using a form or a dialog box. to accept input parameters to search in those fields.
for example if i wish to search the data between 06/06/2004 and 07/07/2004 can a dialog box pops up and asks for begining date where i can enter 06/06/2004 and a second dialog box pops up and askd for ending date where i enter 07/07/2004. then the view or stored procedure or query should return all the data in that particular period?
Thanx in advance
vee yes|||foo is the canonical example of a metasyntactic variable (http://catb.org/~esr/jargon/html/M/metasyntactic-variable.html)

you will have to write some application code to generate those dialog boxes|||Is there a reason you posted this twice?

http://www.dbforums.com/showthread.php?t=1118392

There is no way to do what you want. Forms and dialog boxes are a function of the user interface, and MSSQL is purely a database server.|||Sorry i am new to this site, Made a mistake posting it twice. I apologise for it.|||Wow r937, thanks for the new word. Yeeeeee Haw! Since foo bar really does mean something, does that make it a multidimensional-metasyntactic-variable? Or does the space invalidate it?

This is FreakingOutrageouslyOstentatious BeyondAllReality...|||foo is the canonical example of a metasyntactic variable (http://catb.org/~esr/jargon/html/M/metasyntactic-variable.html)

you will have to write some application code to generate those dialog boxes

So, its like a doo-hicky or a thing-a-ma-bob?|||yup

or a widget, doodad, thingie, gizmo, gadget, dingus, gewgaw, knickknack, whatnot, bric-a-brac, folderol, jigger, gimmick, dingbat, thingamajig, thingum, contraption, whatchamacallit, or whatsis|||There is no such facility in MSSQL Server to have that option of getting input dynamically at runtime.
However you can make use of variables which can take the values from the existing tables of the database if at all they are available.

Declare @.startdate date,
@.enddate date
go
select @.startdate=table.start_date, @.enddate=table.end_date from table where sales_id = '111221'

now you can use these values in your script.

if you dont have any such table then create a table wth two fields
start_date and end_date and every time update the date fields in the table so that your stored procedure takes the values from there

you can update the date fields by simply opening the table in enterprise manager changing the values and executing agin freshly

hope this helps
:)