Database

Return 0 when no rows [modified]

17-Jul-06 22:42

revert to most recent backup, or prepare to empty your wallet..

or try going through MS details on how to troubleshoot it.

How to troubleshoot and repair a damaged Jet 4.0 database in Access 2000:

http://support.microsoft.com/kb/209137/[^]

Heinz_15-Jul-06 13:49

Heinz_

15-Jul-06 13:49

Hi, how can i return a 0 if my query returns no results?

I'm trying to do this because i have a subquery inside a query but if the subquery returns no values then the main query also returns nothing.
I'm using MySQL 5.

Thanks in advance.

-- modified at 20:24 Saturday 15th July, 2006

Re: Return 0 when no rows

ra ra ra ra16-Jul-06 23:26

Re: Return 0 when no rows [modified]

16-Jul-06 23:26

sounds like you are using an inner join between your queries? try a left or right join.

or post you sql and you may get a better response

Heinz_17-Jul-06 9:30

Heinz_

17-Jul-06 9:30

Hi, you're right, there's a inner join inside my query, check it out, hope you understand the query and the problem:

select SUM((t1.valor + t1.total) - t2sum) from consultas as t1 inner join (select consulta, SUM(cantidad_pagada) as t2sum from pagos_consultas where fecha_pago='2006-07-05' group by consulta) as t2 on t2.consulta=t1.id where t1.nula=0 and t1.pagada=0 and t1.fecha='2006-07-05'

I had no idea about left and right join so i went to http://www.w3schools.com/sql/sql_join.asp and took a read, the left join is the one for me! but this will return NULL in the second table when there're no matches and if you look at my query in "(t1.valor + t1.total) - t2sum" i do a " - " operation and i cant reduce a number with a null value or can i? i must replace that null for a 0.

Thanx man.

-- modified at 15:42 Monday 17th July, 2006

Re: Return 0 when no rows

Ennis Ray Lynch, Jr.17-Jul-06 12:42

Ennis Ray Lynch, Jr.

17-Jul-06 12:42

isnull( field, newValue) works in SQL Server and NVL in Oracle.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: Return 0 when no rows [modified]

ra ra ra ra17-Jul-06 22:22

Re: Return 0 when no rows [modified]

17-Jul-06 22:22

you can use also use the coalesce() function to remove the Null value.

eg.

replace - t2sum

with

- coalesce(t2sum,0)

is a nice little function is coalesce as you can list as many variables as you want, it works through from left to right until it finds a non null value and uses that.

sounds like its the same as the isNull() function mentioned in previous post.

its a SQL server only function though!

-- modified at 4:23 Tuesday 18th July, 2006

Heinz_18-Jul-06 8:27

Heinz_

18-Jul-06 8:27

Thanx man. You really did it! thanx.
I was reading mysql 5 manual and there's a ISNULL(expression) function but it returns 1 if the expression is null and 0 if not null (the inverse of what i need), but, thank god there's also a IS NOT NULL wich return 1 when not null and 0 when null, so i have now many ways to acomplish my objective.

[ADDED] Reading about COALESCE() in mysql 5, and it works in a different manner than the normal ISNULL for MS SQL SERVER and others...COALESCE does not replace any null values nor it has a fixed return value, COALESCE() in mysql takes a list of values as a parameter and returns the first Not NULL value and returns NULL if any of the values in the list are not null, ie:

mysql> SELECT COALESCE(NULL,NULL,1,'hello','world',25);<br />
        -> 1<br />
mysql> SELECT COALESCE(NULL,NULL,NULL);<br />
        -> NULL

So, all i have to do in my case is: COALESCE(mycolumnvalue,0) and if the query returns mycolumnvalue null then it will return 0.

Bye,

-- modified at 14:52 Tuesday 18th July, 2006

can we create stored procedures in MS Access database

ChennaiBabu14-Jul-06 21:15

ChennaiBabu

14-Jul-06 21:15

hi all,

greetings.I have a doubt regarding the creation and execution of stored procedures.Can any one tell me how to create a stored procedure in MS Access database and how to execute it?

Thanks in advance.

Babu

Re: can we create stored procedures in MS Access database

mysorian15-Jul-06 4:43

Re: can we create stored procedures in MS Access database

15-Jul-06 4:43

You need to create an Access Project. You may refer to my article on this at:
http://www.angelfire.com/vt2/hodentek/[^]

This being said, Access 2003 Cool | :cool:

does not support Access project with SQL 2005 Server.

ra ra ra ra17-Jul-06 22:28

stored procedure Output parameter value is Null

17-Jul-06 22:28

Simple answer - NO

unless as mentioned by someone else you are creating an MS access project to hook up to a sql server, if so its just a SQL server stored proc

But if its a normal access database you should be able to write your own scalar functions (single return value), but you can not write functions that will return a recordset. unless the consumer of the recordset is another vba function...

hope that makes sense, I've not had my coffee yet!

Mr ra ra

For_IT14-Jul-06 12:26

Re: stored procedure Output parameter value is Null

14-Jul-06 12:26

Hi
I am using following stored procedure
-----------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetUserControlTemplateCode]
@product_id int,
@genmask_code nvarchar(max) output
AS
BEGIN
SET NOCOUNT ON;

Select dbo.cp_product.product_type, dbo.cp_genmask.genmask_code
from dbo.cp_genmask inner join dbo.cp_product
on dbo.cp_genmask.genmask_product_type=dbo.cp_product.product_type
where dbo.cp_product.product_id=@product_id

END
--------------------------------
when I execute the above code using

exec GetUserControlTemplateCode 1,'x'

I get correct values for x i.e. output parameter.

My code behind to call stored procedure is as follows

---------------------------------
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sConnectionString2"].ConnectionString);
SqlCommand Com = con.CreateCommand();
Com.CommandType = CommandType.StoredProcedure;
Com.CommandText = "GetUserControlTemplateCode";

//Create parameter object to provide input
SqlParameter parInput = Com.Parameters.Add("@product_id", SqlDbType.Int);
parInput.Direction = ParameterDirection.Input;
parInput.Value = nProductID;

// Create parameter to hold output
SqlParameter parOutput = Com.Parameters.Add("@genmask_code", SqlDbType.NVarChar,300);
parOutput.Direction = ParameterDirection.Output;

//Open the connection
con.Open();
//Execute command
try
{
Com.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}

string sTemplateCode = Convert.ToString(parOutput.Value);
sTemplateCode = sTemplateCode.Replace("ProductID", nProductID.ToString());
Control myControl = ParseControl(sTemplateCode);
PlaceHolderForProducts.Controls.Add(myControl);
con.Close();
}
-----------------------------------

when I execute the code, i do not get any exception, but the value of output parameter is null.

Can someone please help me to understand whats missing here?

Thanks.

nguyenvhn14-Jul-06 16:24

nguyenvhn

14-Jul-06 16:24

I did not see the statement setting value for @genmask_code so infact, it have no return value. Also I think exec GetUserControlTemplateCode 1,'x' will not work because of lacking OUTPUT modifier. It should be:

<br />
exec GetUserControlTemplateCode 1, @x OUTPUT<br />
print @x<br />

Re: stored procedure Output parameter value is Null

Colin Angus Mackay14-Jul-06 21:53

Colin Angus Mackay

14-Jul-06 21:53

Your stored procedure returns a set of data. The result set contains two columns, product_type and genmask_code. Use ExecuteReader() or use a DataAdapter to get the information out, or change the stored procedure.

To populate the @genmask_code output parameter you can do something like this:

SELECT @genmask_code = dbo.cp_genmask.genmask_code
FROM ...

NOTE: You should only do this if you expect the SELECT statement to retrieve only one row.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: stored procedure Output parameter value is Null

Mike Dimmick15-Jul-06 3:57

Mike Dimmick

15-Jul-06 3:57

Colin Angus Mackay wrote:
NOTE: You should only do this if you expect the SELECT statement to retrieve only one row.

You won't get an error if it does retrieve more than one row, but if it does, you'll simply get the value from the last row returned by the query. Unless you put an ORDER BY clause in, however, which row that is will be unpredictable.

If no rows are returned, the value of the variable will be unchanged.

Stability. What an interesting concept. -- Chris Maunder

Re: stored procedure Output parameter value is Null

For_IT16-Jul-06 11:05

Re: stored procedure Output parameter value is Null

16-Jul-06 11:05

Thanks for your replies,

I changed my stored procedure as follows to assign the value to output variable:

ALTER PROCEDURE [dbo].[GetUserControlTemplateCode]
@product_id int,
@genmask_code nvarchar(max) output
AS
BEGIN
SET NOCOUNT ON;

Select dbo.cp_product.product_type, @genmask_code=dbo.cp_genmask.genmask_code
from dbo.cp_genmask inner join dbo.cp_product
on dbo.cp_genmask.genmask_product_type=dbo.cp_product.product_type
where dbo.cp_product.product_id=@product_id

END

when I execute this I get following error:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

where should I do data assignment operation?

For_IT16-Jul-06 12:18

Problem accessing a SSIS Package

16-Jul-06 12:18

I changed the code as follows

ALTER PROCEDURE [dbo].[GetUserControlTemplateCode]
@product_id int,
@genmask_code nvarchar(max) output
AS
BEGIN
SET NOCOUNT ON;
Declare @product_type int

Select b.product_type, @genmask_code=dbo.cp_genmask.genmask_code
from dbo.cp_genmask as a inner join dbo.cp_product as b
on a.genmask_product_type=b.product_type
where b.product_id=@product_id
END

I executed this code using using

exec GetUserControlTemplateCode 1, @genmask_code output
print @genmask_code

It gives me message as command executed successfully, but I can not see any data in output window. Again when I call this procedure from code behind code it shows null value...

Please suggest where I am wrong.

mysorian14-Jul-06 6:04

Re: Problem accessing a SSIS Package

14-Jul-06 6:04

My evaluation copy ran-out and I uninstalled the previous and

installed another evaluation edition. The installation was

successful. I created a SSIS package importing a table from Oracle

10G XE to the SQL 2005 on my machine. I saved the package on

the server. The SSIS service is running but when I try to refresh

the node, I get this message. The help link leads to no where(Hello, Microsoft!):

--------------------------------------

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request.

(Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?

ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Login timeout expired
An error has occurred while establishing a connection to the

server. When connecting to SQL Server 2005, this failure may be

caused by the fact that under the default settings SQL Server

does not allow remote connections.
TCP Provider: No connection could be made because the target

machine actively refused it. (Microsoft SQL Native Client)

------------------------------

Login timeout expired
An error has occurred while establishing a connection to the

server. When connecting to SQL Server 2005, this failure may be

caused by the fact that under the default settings SQL Server

does not allow remote connections.
TCP Provider: No connection could be made because the target

machine actively refused it. (Microsoft SQL Native Client)

------------------------------

My efforts so far:

:-DI have verified the items in the Surface area screen, everythign

looks good.
SSIS service has started.

:-DBoth the Network Server TCP/IP dynmaic port and the SQL

Client TCP/IP dynamic port are the same.

:-DI have no problem logging in to any of the services.

Mad | :mad:

But I get this message.

Mad | :mad:

I tried to create a package from Visual Studio, but that also did

not succeed.

Roll eyes | :rolleyes:

In my previous set up, I created perhaps 1/ 2 dozen different

packages.
I am puzzzled.

I appreciate if some one could throw some light.

mysorian14-Jul-06 7:54

Re: Problem accessing a SSIS Package

14-Jul-06 7:54

One of my statements is wrong.

I could create a package in Visual Studio, but I could not access this in SSIS.

Dave Kreskowiak14-Jul-06 9:30

Dave Kreskowiak

14-Jul-06 9:30

mysorian wrote:
My evaluation copy ran-out and I uninstalled the previous and installed another evaluation edition

Do you want to read that again and guess what a probably cause of the problem is? Do you think it's entirely possible that the new installation knows that you already had an evaluation edition installed and it expired?

Dave Kreskowiak
Microsoft MVP - Visual Basic

Re: Problem accessing a SSIS Package

mysorian15-Jul-06 4:39

Differance between '@variable' and '@@variable' in SQL

15-Jul-06 4:39

The evaluation edition was completely removed as any new install flags any remnants that needs removing. I was instructed during new install to move my 'Data' to another location which I did. The packages although saved to 'Sql Server' had created 'dtsx' files as well.

However, from VS 2005 I can create a package, but cannot see, or access it in the SQL 2005 Management Studio. I wish error messages could have been more friendly [see my comments in the article on SQLCMD].

I am technical writer and I routinely, meticulously document what I do. Here are my recent articles.

Thnaks for your fast response.

Moving Data from SQL Server 2000 to SQL Server 2005
Querying Databases in SQL Server 2005
SQL2000 Data Transfer is a snap with SQL 2005
On using the SQLCMD in SQL 2005 Server
Generating and Hosting a SQL Server Reporting Services Report Using SQL Server 2005 Business Intelligence Development Studio

These and other articles may be found at:http://www.angelfire.com/vt2/hodentek/[^]

Sampath Eleperuma14-Jul-06 4:09

Sampath Eleperuma

14-Jul-06 4:09

What is the Differance between '@variable' and '@@variable' in SQL syntax?

Thanks
Sampath

Re: Differance between '@variable' and '@@variable' in SQL

Eric Dahlvang14-Jul-06 5:12

Eric Dahlvang

14-Jul-06 5:12

@variable are local variables

@@WHATEVER are system variables

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

numeric data type value

For_IT14-Jul-06 0:06