SQL query returning zero row in MySQL 5 - Database Discussion Boards

Re: Data Recovery - After Drop a table and creating the same table

adnanrafiq13-Jun-07 22:04

adnanrafiq

13-Jun-07 22:04

Thanks for Helping to forget about it .

Many Thanks,
Adnan Rafiq
muhammadadnanrafiq@gmail.com

Procedure for Column and Table variable

Elena200612-Jun-07 21:54

Elena2006

12-Jun-07 21:54

Hi All,

There is a requirement to create a procedure with passing Table/View name, column names in a dynamic manner with a return of resultset, It is require both in SqlServer and Sybase.

For example I am creating here one procedure:

alter procedure dba.TestProc(in viewname varchar(30),in orderbycol varchar(30),in orderbytype integer)
/* RESULT ( column-name,... ) */
begin
if orderbytype = '1' then
execute immediate 'select '+orderbycol+' from '+viewname+' order by cast('+orderbycol+' as numeric)'
else
execute immediate 'select '+orderbycol+' from '+viewname+' order by '+orderbycol
end if
end

From above procedure I am getting the error when I am using in my .NET application that it is not returning Resultset.

Suppose I want to make a procedure for:

Select dynamicCol1, dynamicCol2 from dynamicTable

How I will create the procedure for above with passing dynamicCol1,dynamicCol2 and dynamicTable variable and how I will call in .NET application.

With regards

Re: Procedure for Column and Table variable

Harini N K12-Jun-07 22:25

Harini N K

12-Jun-07 22:25

Hi
Try this in Northwind database. Assume you are passing two parameters @OrderBycol and @viewname from the front end.

use northwind<br />
go<br />
<br />
DECLARE @sql nvarchar(4000)<br />
DECLARE @OrderByCol nvarchar(4000)<br />
DECLARE @viewname nvarchar(100) <br />
<br />
set @viewname  = 'Invoices' -- passed from front end <br />
set @OrderByCol = 'ShipName, ShipAddress' -- passed from front end <br />
<br />
SET @sql = 'SELECT ' +  @OrderByCol + ' FROM ' + @viewname<br />
<br />
print @sql<br />
<br />
EXECUTE (@sql)

and make sure that you have given one space after SELECT in 'SELECT ' and also before & after the keyword FROM clause.

Harini

Re: Procedure for Column and Table variable

Elena200612-Jun-07 22:36

Elena2006

12-Jun-07 22:36

Thanks a lot.

I will try it. Have you any idea how I will create it in Sybase.

Is there any problem that to call the Procedure in front-end as it returns a result set not a single value?

Re: Procedure for Column and Table variable

Harini N K12-Jun-07 22:43

Harini N K

12-Jun-07 22:43

Hi
I have not worked on Sybase.

I dont get you the last line ...

Harini

Please Solve this SQL Query

Care Career12-Jun-07 18:01

Care Career

12-Jun-07 18:01

Hi

Everyone

Two Tables r there.
Emp-ENO->1,2,3
Ename->A,B,C

Score-ENO->1,2,3
S1->70,96,80
S2->75,65,75
S3->90,,100
In Score Table fields ENO,Subject1,S2,S3 r there shown above.

Please help.I want to display those employee names who got same marks in Subject 2 like 75 in S2.

Please help for this query.

Thanks
CARECAREER

Re: Please Solve this SQL Query

Krish - KP12-Jun-07 18:19

Krish - KP

12-Jun-07 18:19

check whether this solvs
select * from score where s2 in (
select s.s2
from emp e inner join score s on e.id = s.id
group by s.s2 having count(*) > 1
)

Regards
KP

SQL query returning zero row in MySQL 5

Guffa12-Jun-07 8:57

Guffa

12-Jun-07 8:57

My web hotel has upgraded from MySQL 4.1.21 to MySQL 5.0.37. The only problem I have had with this is that a query doesn't return anything in the result any more:

select sum(i.FileSize) as TotalSize<br />
from Photos p<br />
inner join Images i on i.PhotoId=p.PhotoId and i.IsPublic=1<br />
where p.IsPublicArt=1 or p.IsPublicCommon=1

If I change "sum" to "max" or "count" the query return one row, with "sum" or "avg" it returns zero rows.

FileSize is int and can not be null.

If I test the query directly in the database (using phpmyadmin) it returns one row with all the aggregates.

My connection string looks like this (with a different uid and pwd of course): "driver={MySQL};server=mysql.loopia.se;uid=asdfasdf;pwd=asdfasdf;database=voidstation_com"

I have tried to add ";option=3" to the connection string, but that didn't make any difference.

The list of changes that the web hotel linked to:
http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html[^]
I don't see anything there that should affect this.

Anybody got any ideas?

---
single minded; short sighted; long gone;

Re: SQL query returning zero row in MySQL 5

Arun.Immanuel12-Jun-07 15:53

Arun.Immanuel

12-Jun-07 15:53

Guffa wrote:
If I change "sum" to "max" or "count" the query return one row, with "sum" or "avg" it returns zero rows.

I do not know about MySql.
But see what you can do with this hint:
sum will return a large value than count or max

Regards,
Arun Kumar.A

Re: SQL query returning zero row in MySQL 5

Guffa12-Jun-07 16:15

Guffa

12-Jun-07 16:15

Thanks for your reply.

When I use sum and run the query in phpmyadmin, the exact value that it returns is 609538140. It's larger than what count and max returns, but it's still way within the bounds of an int.

Anyway, it's not a problem with reading the value from the row, as the query doesn't even return a row. The result is empty. It doesn't contain any row to read from.

---
single minded; short sighted; long gone;

Solved

Guffa12-Jun-07 16:28

Guffa

12-Jun-07 16:28

When I changed the driver used for the connection from version 2.50 to 3.51, it started working again.

Smile | :)

---
single minded; short sighted; long gone;

Reporting Services 2005 Express

Jonathan Snyder12-Jun-07 3:08

Jonathan Snyder

12-Jun-07 3:08

I am using

Reporting Services 2005 Express
SQl Express
Visual Studio 2005

The project is in asp.net.

I have .jpg signature images that are stored in an directory image folder that I need to dynamically add to my reports based on the parameter of the person's username that is stored in a User's Table. What do I need to do to add these images dynamically? If possible.

How to hide the SUM @ the Table Name

MS Lee12-Jun-07 2:20

MS Lee

12-Jun-07 2:20

hello to al,
i am using the SUM(),
but
for eg if i give SUM(PENCOST) means it gives then sum of cost of the pens
but the table name also displays as SUM of PENCOST instead of PENCOST,, Confused | :confused:

so what should and how should i give the code.......????

self confidence+hard work=SUCCESS

Re: How to hide the SUM @ the Table Name

Revathij12-Jun-07 2:34

Revathij

12-Jun-07 2:34

give Sum(PenCost) as PenCost

Re: How to hide the SUM @ the Table Name

MS Lee12-Jun-07 2:38

MS Lee

12-Jun-07 2:38

HEY UU ITS WORKING YAAR,

THANK U VERY MUCH REVATHI,,
THANKS A LOT

self confidence+hard work=SUCCESS

Re: How to hide the SUM @ the Table Name

MS Lee13-Jun-07 18:29

MS Lee

13-Jun-07 18:29

some times we hv to give Sum(COST) COST (we hv to give spae instead of 'as')

self confidence+hard work=SUCCESS

Sql joins

Tauseef A12-Jun-07 1:56

Tauseef A

12-Jun-07 1:56

hi guys ,,,

actually i am very much new to oracle, i just want to

ask that cam we use sql joins in oracle
if not than how can we get data from different tables

based on a particular chriteria,,,
thanks in advance

hello

Re: Sql joins

Krish - KP12-Jun-07 2:07

Krish - KP

12-Jun-07 2:07

Yes.
for fetching data from more than one table, Joins can be used in Oracle.

Regards
KP

Need Sample Stored procedures

Krishnaraj Barvathaya B12-Jun-07 1:48

Krishnaraj Barvathaya B

12-Jun-07 1:48

Hi All,

I need a URL from where I can download sample stored procedures, with which I can learn different techniques to write stored procedures. We have 101 Sample C# codes available to download in MSDN site, I am looking for similar kind of with respect to stored procedures.
Can any body help me….

Thanks in advance

Regards,
Krishnaraj

Re: Need Sample Stored procedures

Sathesh Sakthivel12-Jun-07 2:09

Sathesh Sakthivel

12-Jun-07 2:09

Stored Procedures[^]

Regards,

Satips. Rose | [Rose]

Re: Need Sample Stored procedures

MS Lee12-Jun-07 2:26

MS Lee

12-Jun-07 2:26

hello krishna, can u tell how to get that 101 sample c# codes from MSDN site, can u give me the link?

self confidence+hard work=SUCCESS

INT and VARCHAR

N a v a n e e t h12-Jun-07 1:34

N a v a n e e t h

12-Jun-07 1:34

Please make me clear that

will INT(4) and VARCHAR(4) will consume same storage space in SQL Server 2000 ?

Navaneeth
My Website

Re: INT and VARCHAR

Sathesh Sakthivel12-Jun-07 2:07

Sathesh Sakthivel

12-Jun-07 2:07

Navaneeth. wrote:
will INT(4) and VARCHAR(4) will consume same storage space in SQL Server 2000 ?

No.

For More Details Here[^]

Regards,

Satips. Rose | [Rose]

How to Customized SQL Report Viewer Control

Ali Raza Shah11-Jun-07 18:38

Ali Raza Shah

11-Jun-07 18:38

How to Customized SQL Report Viewer Control ? e.g The Export Combo Should only convert the Report to PDF and User Cannot Convert it to another Format
Plz Help me!

Ali

Pervasive SQL 8.0

Soleil couchant11-Jun-07 17:00

Soleil couchant

11-Jun-07 17:00

Hi there,

anyone using Pervasive SQL 8.0??

I have an issue with transactions using C# on Pervasive SQL 8.0. The transaction does not wait when there is a lock (another transaction operating) on the table.

It just throws an exception.

Thanks for helping

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.