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
>
>

Retrieving scanned images

Hi all
I have a scenario where I need to retrieve scanned images for a report. The
plan is to have one page showing items submitted from a table and the
remaining pages producing the linked scanned images. the scanned images will
probably be PDF or data held in XML format. I am thinking the jmp to URL
function or something similar will be used here
thank you
Mickeyyou can use the Image item from the toolbox, drag it onto the report and
follow the wizard.
"Mickey N" <MickeyN@.discussions.microsoft.com> wrote in message
news:46EB0E8F-C0D7-498F-82FB-F3AF9C36270E@.microsoft.com...
> Hi all
> I have a scenario where I need to retrieve scanned images for a report.
> The
> plan is to have one page showing items submitted from a table and the
> remaining pages producing the linked scanned images. the scanned images
> will
> probably be PDF or data held in XML format. I am thinking the jmp to URL
> function or something similar will be used here
> thank you
> Mickey

retrieve popular search items from table - problems

I've been using SQL for a while but I'm kinda stumped as to where to start with this one. I have a search function on my ecommerce site and whatever anyone searches for is being stored in a database.

I need a script that will look at my search entries table and return a list of the most popular search terms.

So go to the table and produce result like

Search Term (count)
Harry Potter (6)
Sherlock Holmes (4)
Garfield (2)

But like I say, I'm a little stumped as to where to even begin with this one.

You can use a GROUP BY and ORDER BY clause in your SQL statement e,g,

DECLARE @.MYTABLETABLE (idint IDENTITY(1,1), SearchTermvarchar(10))INSERT @.MYTABLEVALUES('ASP.NET')INSERT @.MYTABLEVALUES('ASP.NET')INSERT @.MYTABLEVALUES('Something')INSERT @.MYTABLEVALUES('Nothing')INSERT @.MYTABLEVALUES('Anything')INSERT @.MYTABLEVALUES('Other')SELECT SearchTerm,COUNT(id)AS SearchesFROM @.MyTableGROUP BY SearchTermORDER BYCOUNT(id)DESC

Retrieve ONLY the Report Items that a user has permissions for

Using ListChildren(@."\",true) returns all items in the report server that a
user has access to, however if the user does not have access to view the home
folder, the call fails.
How do I retrieve ALL the folders/reports that a user has permission for
from an application running with the users default credentials?You would have to write or run the program with an admin account & then once
you get a list of all the children,
you'll have to loop through all of them to determine which one belong to the
X-User.
Edgar,
"DaveH" wrote:
> Using ListChildren(@."\",true) returns all items in the report server that a
> user has access to, however if the user does not have access to view the home
> folder, the call fails.
> How do I retrieve ALL the folders/reports that a user has permission for
> from an application running with the users default credentials?

Retrieve good records from a bad record table

I have a situation where I need a table if bad items to match to. For
example, The main table may be as:

Table Main:
fd_Id INT IDENTITY (1, 1)
fd_Type VARCHAR(100)

Table Matcher:
fd_SubType VARCHAR(20)

Table Main might have a records like:
1 | "This is some full amount of text"
2 | "Here is half amount of text"
3 | "Some more with a catch word"

Table Matcher:
"full"
"catch"

I need to only get the records from the main table that do not have
anything in the match table. This should return only record 2.Verticon:: (miben@.miben.net) writes:
> I have a situation where I need a table if bad items to match to. For
> example, The main table may be as:
> Table Main:
> fd_Id INT IDENTITY (1, 1)
> fd_Type VARCHAR(100)
> Table Matcher:
> fd_SubType VARCHAR(20)
> Table Main might have a records like:
> 1 | "This is some full amount of text"
> 2 | "Here is half amount of text"
> 3 | "Some more with a catch word"
> Table Matcher:
> "full"
> "catch"
> I need to only get the records from the main table that do not have
> anything in the match table. This should return only record 2.

SELECT mn.fd_id, mn.fd_Type
FROM tablemain mn
WHERE NOT EXISTS (SELECT *
FROM tablematcher mt
WHERE md.fd_Type LIKE '%' + mt.fd_SubType + '%')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Retrieve Deleted Items > Log

Looking for a little assistance with a big problem.

I lost some data from a query that went wrong. I still have the logfile though, is there anyway of retrieving the data from the log file (.LDF) so that I can undo what someone did?

I located some 3rd party software but they are big bucks and the demos won't allow you to do it..

Thanks in advance,

JayThe safest thing would be to backup the transaction log, then restore the database froma full backup with norecovery, then restore the transaction log with 'stopat = time' where time is just before the bad query was run. This assumes that you are in full recovery mode, however.

Alternatively, if you have the disk space, you can restore the database to a new location, and try to transfer some of the lost data over. This is much more risky, as you have to be sure you get all of the relationships, id values, etc. right. Most vendors will invalidate their support contracts, if you do this sort of thing to their databases.