[TCP/IP Sockets]ConnectionCheckForData (CheckforData())

MS SQL 2000.
When I attempt a SELECT I get the following:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
The offending SELECT
select a.account
from table1 a
where a.doc = '123456A'
and (select count(1)
from table1 b
where a.account = b.account
and a.doc = b.doc
and a.oseq = b.oseq
and a.party = b.party) > 1
If I do the SELECT with out the sub select it works fine. Suggestions?
Hi Scott
Having DDL and sample data with the expected results from that data would be
nice. See http://www.aspfaq.com/etiquette.asp?id=5006
"Scott Sheen" wrote:

> MS SQL 2000.
> When I attempt a SELECT I get the following:
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
> The offending SELECT
> select a.account
> from table1 a
> where a.doc = '123456A'
> and (select count(1)
> from table1 b
> where a.account = b.account
> and a.doc = b.doc
> and a.oseq = b.oseq
> and a.party = b.party) > 1
If this worked it wouldreturn multiple rows of the same account number

>
How about:
SELECT a.account
FROM table1 a
JOIN table1 b ON a.account = b.account
and a.doc = b.doc
and a.oseq = b.oseq
and a.party = b.party
WHERE a.doc = '123456A'
GROUP BY a.account
HAVING COUNT(*) > 1

> If I do the SELECT with out the sub select it works fine. Suggestions?
>
John
|||Reworking the statement worked around the issue.
=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote:
>Hi Scott
>Having DDL and sample data with the expected results from that data would be
>nice. See http://www.aspfaq.com/etiquette.asp?id=5006
>"Scott Sheen" wrote:
>
>If this worked it wouldreturn multiple rows of the same account number
>How about:
>SELECT a.account
>FROM table1 a
>JOIN table1 b ON a.account = b.account
> and a.doc = b.doc
> and a.oseq = b.oseq
> and a.party = b.party
>WHERE a.doc = '123456A'
>GROUP BY a.account
>HAVING COUNT(*) > 1
>
>John
|||Hi Scott
"Scott Sheen" wrote:

> Reworking the statement worked around the issue.
>
Was this the statement I posted or something different? How many I/O is it
doing?
John

[TCP/IP Sockets]ConnectionCheckForData (CheckforData())

MS SQL 2000.
When I attempt a SELECT I get the following:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionChe
ckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
The offending SELECT
select a.account
from table1 a
where a.doc = '123456A'
and (select count(1)
from table1 b
where a.account = b.account
and a.doc = b.doc
and a.oseq = b.oseq
and a.party = b.party) > 1
If I do the SELECT with out the sub select it works fine. Suggestions?Hi Scott
Having DDL and sample data with the expected results from that data would be
nice. See http://www.aspfaq.com/etiquette.asp?id=5006
"Scott Sheen" wrote:

> MS SQL 2000.
> When I attempt a SELECT I get the following:
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionC
heckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
> The offending SELECT
> select a.account
> from table1 a
> where a.doc = '123456A'
> and (select count(1)
> from table1 b
> where a.account = b.account
> and a.doc = b.doc
> and a.oseq = b.oseq
> and a.party = b.party) > 1
If this worked it wouldreturn multiple rows of the same account number

>
How about :
SELECT a.account
FROM table1 a
JOIN table1 b ON a.account = b.account
and a.doc = b.doc
and a.oseq = b.oseq
and a.party = b.party
WHERE a.doc = '123456A'
GROUP BY a.account
HAVING COUNT(*) > 1

> If I do the SELECT with out the sub select it works fine. Suggestions?
>
John|||Reworking the statement worked around the issue.
examnotes <jbellnewsposts@.hotmail.com> wrote:
>Hi Scott
>Having DDL and sample data with the expected results from that data would b
e
>nice. See http://www.aspfaq.com/etiquette.asp?id=5006
>"Scott Sheen" wrote:
>
>If this worked it wouldreturn multiple rows of the same account number
>
>How about :
>SELECT a.account
>FROM table1 a
>JOIN table1 b ON a.account = b.account
> and a.doc = b.doc
> and a.oseq = b.oseq
> and a.party = b.party
>WHERE a.doc = '123456A'
>GROUP BY a.account
>HAVING COUNT(*) > 1
>
>John|||Hi Scott
"Scott Sheen" wrote:

> Reworking the statement worked around the issue.
>
Was this the statement I posted or something different? How many I/O is it
doing?
John

[TCP/IP Sockets]ConnectionCheckForData (CheckforData())

MS SQL 2000.
When I attempt a SELECT I get the following:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
The offending SELECT
select a.account
from table1 a
where a.doc = '123456A'
and (select count(1)
from table1 b
where a.account = b.account
and a.doc = b.doc
and a.oseq = b.oseq
and a.party = b.party) > 1
If I do the SELECT with out the sub select it works fine. Suggestions?Reworking the statement worked around the issue.
=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote:
>Hi Scott
>Having DDL and sample data with the expected results from that data would be
>nice. See http://www.aspfaq.com/etiquette.asp?id=5006
>"Scott Sheen" wrote:
>> MS SQL 2000.
>> When I attempt a SELECT I get the following:
>> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionCheckForData
>> (CheckforData()).
>> Server: Msg 11, Level 16, State 1, Line 0
>> General network error. Check your network documentation.
>> Connection Broken
>>
>> The offending SELECT
>> select a.account
>> from table1 a
>> where a.doc = '123456A'
>> and (select count(1)
>> from table1 b
>> where a.account = b.account
>> and a.doc = b.doc
>> and a.oseq = b.oseq
>> and a.party = b.party) > 1
>If this worked it wouldreturn multiple rows of the same account number
>How about:
>SELECT a.account
>FROM table1 a
>JOIN table1 b ON a.account = b.account
> and a.doc = b.doc
> and a.oseq = b.oseq
> and a.party = b.party
>WHERE a.doc = '123456A'
>GROUP BY a.account
>HAVING COUNT(*) > 1
>
>> If I do the SELECT with out the sub select it works fine. Suggestions?
>>
>Johnsql

[SQLDatasource]Dynamic selection of column

Hey, I have a search form with a selectbox. This selectbox contains the columnnames.
I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session.

If you see I have put in the querystring as columnname @.sescolumn which I have initialised as asp:sessionparameter.
But it gives no results. When I put @.sescolumn between [] like normal columnnames are it doesn't work also.

Can someon put my on the right path?

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring%>" SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @.sescolumn like @.seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" > <SelectParameters> <asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" /> <asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" /> <asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" /> <asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" /> </SelectParameters> </asp:SqlDataSource>

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where ((@.sescolumn='AutoID' AND [AutoID] LIKE @.seskeyword) OR (@.sescolumn='Merk' AND [Merk] LIKE @.seskeyword) OR (@.sescolumn='Kleur' AND [Kleur] LIKE @.seskeyword) OR (@.sescolumn='Type' AND [Type] LIKE @.seskeyword) OR (@.sescolumn='Autotype' AND [Autotype] LIKE @.seskeyword) OR (@.sescolumn='prijs' AND [prijs] LIKE @.seskeyword) OR (@.sescolumn='Zitplaatsen' AND [Zitplaatsen] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding1' AND [Afbeelding1] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding2' AND [Afbeelding2] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding3' AND [Afbeelding3] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding4' AND [Afbeelding4] LIKE @.seskeyword)) and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>

Or...

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where CASE @.sesscolumn WHEN 'AutoID' THEN [AutoID] WHEN 'Merk' THEN [Merk] WHEN 'Kleur' THEN [Kleur] WHEN 'Type' THEN [Type] WHEN 'Autotype' THEN [Autotype] WHEN 'prijs' THEN [prijs] WHEN 'Zitplaatsen' THEN [Zitplaatsen] WHEN 'Afbeelding1' THEN [Afbeelding1] WHEN 'Afbeelding2' THEN [Afbeelding2] WHEN 'Afbeelding3' THEN [Afbeelding3] WHEN 'Afbeelding4' THEN [Afbeelding4] END LIKE @.seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>

|||

Damn didn't knew that this was possible. Thanks a lot mate.

Now I have just to convert some columns in where clause to type of string because my session is a string.
Going to find this out

Thanks again

[SQLCE Mobile]Do Select during Transaction

Hi,

I need to do a Select and after a Insert during a Transaction. But when i want to commit this transaction i've the following error message :

The transaction can not be committed if there are any opened cursors in the scope of this transaction. Make sure all data readers / result sets are explicitly closed before committing the change.

I don't understand this error message. How can i close my cursors.

Before my Insert i'm closing my SqlCeDataReader and i dispose it. My SqlCeCommand are also closed and dispose.

Do you have any ID?

Thanks.

Hi Kardath,

I tried to repro this here and could not reproduce the problem. I am able to have both INSERT and SELECT in a single transaction. Can you please try to dispose the all the sqlce objects and see if the problem disappers?

Thanks,

Laxmi

[SQL2k5] Dynamic SQL Query Select on all user tables

In one query, I would like to query every user table in a specified database for

SELECT TOP (3) COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
GROUP BY [Target IP]
ORDER BY EventNum DESC

How is this possible?

Please give examples, I am a beginner.

Assume every table has the same structure for columns event count, target ip, and time.

Forgot, for every table it selects from, the table name must be one of the returned columns.|||

Hi, try the following:

sp_msforeachtable 'SELECT *,''?'' AS TableName FROM (SELECT TOP (3) , COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
FROM ?
GROUP BY [Target IP]
ORDER BY EventNum DESC) SubQuery'

But keep in mind that this should be just used for administrative task and adhoc queries not for business logic as the procedure is not documented, therefore not supported and could be deprecated in the next version of SQL Server.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

[SQL2000] dateadd (millisecond) problem

select dateadd(millisecond,-1,convert(datetime,'20080101 0: 0: 0',112))
why isn't it 20061231 23:59:59.999 ?
--
SoftMedia Technology Co., Ltd.
Website: http://www.softmedia.hk Tel: (852)2743 4228
* TryEasy Accounting/POS/Trading/ERP solutionsThere is a rounding issue associated with millisecond values. Please take a
look at this link for more details:
http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=908
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Man-wai Chang" <info@.softmedia.hk> wrote in message
news:uvGIDscxGHA.4444@.TK2MSFTNGP02.phx.gbl...
> select dateadd(millisecond,-1,convert(datetime,'20080101 0: 0: 0',112))
> why isn't it 20061231 23:59:59.999 ?
Please have a look in BOL at the information about the datetime datatype.
You are assuming that the datatype is accurate to 1 ms - this is not a valid
assumption. You should also review the information in the following link
http://www.karaszi.com/sqlserver/info_datetime.asp

[SQL2000] dateadd (millisecond) problem

select dateadd(millisecond,-1,convert(datetime,'20080101 0: 0: 0',112))
why isn't it 20061231 23:59:59.999 ?
SoftMedia Technology Co., Ltd.
Website: http://www.softmedia.hk Tel: (852)2743 4228
* TryEasy Accounting/POS/Trading/ERP solutionsThere is a rounding issue associated with millisecond values. Please take a
look at this link for more details:
http://www.sql-server-performance.c...tionanswers.com|||"Man-wai Chang" <info@.softmedia.hk> wrote in message
news:uvGIDscxGHA.4444@.TK2MSFTNGP02.phx.gbl...
> select dateadd(millisecond,-1,convert(datetime,'20080101 0: 0: 0',112))
> why isn't it 20061231 23:59:59.999 ?
Please have a look in BOL at the information about the datetime datatype.
You are assuming that the datatype is accurate to 1 ms - this is not a valid
assumption. You should also review the information in the following link
http://www.karaszi.com/sqlserver/info_datetime.asp

[SQL Server 2005] Sub-query and "WITH" CTE, which statement would be faster for database p

1.
===================================================================== SELECT * FROM ( SELECT TOP 20010 *, ROW_NUMBER() OVER( ORDER BY [posttime]
DESC ) AS [rowcounting] FROM [topics] ORDER BY [posttime] DESC ) AS
[temptable] WHERE [rowcounting] > 20000
=====================================================================
and
2.
===================================================================== WITH [temptable] AS ( SELECT TOP 20010 *, ROW_NUMBER() OVER( ORDER BY
[posttime] DESC ) AS [rowcounting] FROM [topics] ORDER BY [posttime] DESC )
SELECT * FROM [temptable] WHERE [rowcounting] > 20000
=====================================================================
Which one will be run faster inside sql server?
Which one can get the most performance?Asked and answered in .programming -- please refrain from multiposting.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Parco" <parco@.nowlover.com> wrote in message
news:eqHzFq5dGHA.380@.TK2MSFTNGP04.phx.gbl...
> 1.
> =====================================================================> SELECT * FROM ( SELECT TOP 20010 *, ROW_NUMBER() OVER( ORDER BY [posttime]
> DESC ) AS [rowcounting] FROM [topics] ORDER BY [posttime] DESC ) AS
> [temptable] WHERE [rowcounting] > 20000
> =====================================================================> and
> 2.
> =====================================================================> WITH [temptable] AS ( SELECT TOP 20010 *, ROW_NUMBER() OVER( ORDER BY
> [posttime] DESC ) AS [rowcounting] FROM [topics] ORDER BY [posttime]
> DESC )
> SELECT * FROM [temptable] WHERE [rowcounting] > 20000
> =====================================================================> Which one will be run faster inside sql server?
> Which one can get the most performance?
>

[SELECT] question

Hi all,
I've got a table structured as follow where the code field identify a particular parameter. Is it possible with a single query to retrieve more than just one parameter at a time? I mean can I get a recordset with the first field representing the date, the second field holding the values of parameter code [01], the third field the values of parameter code [02] and so forth??

Table structure
CREATE TABLE `test` (
`date` datetime NOT NULL ,
`code` varchar(3) NOT NULL ,
`value` float NOT NULL
) TYPE=MyISAM;

Sample data
+-------+--+---+
| date | code | value |
+-------+--+---+
| 2004-06-01 00:00:00 | 001 | 89 |
| 2004-06-01 00:00:00 | 002 | 1 |
| 2004-06-01 01:00:00 | 001 | 76 |
| 2004-06-01 01:00:00 | 002 | 5 |
| 2004-06-01 02:00:00 | 001 | 67 |
| 2004-06-01 02:00:00 | 002 | 7 |
| 2004-06-01 03:00:00 | 001 | 46 |
| 2004-06-01 03:00:00 | 002 | 4 |
| 2004-06-01 04:00:00 | 001 | 43 |
| 2004-06-01 04:00:00 | 002 | 3 |
+-------+--+---+

Recordset
+-------+----+----+
| date | value_cod1 | value_cod2 |
+-------+----+----+
| 2004-06-01 00:00:00 | 89 | 1 |
| 2004-06-01 01:00:00 | 76 | 5 |
| 2004-06-01 02:00:00 | 67 | 7 |
|..................................... ..............|
+-------+----+----+

Thank you very much!
Paolo SaudinTo select all of the columns in an arbitrary order, you can use:SELECT *
FROM testTo select the columns in a specific order, you can use:SELECT date, code, value
FROM test-PatP|||ah, the good old denormalization question

mysql doesn't support full outer join, but this is an equivalent --

select t1.date
, t1.value as value_cod1
, t2.value as value_cod2
from `test` as t1
left outer
join `test` as t2
on t1.date
= t2.date
and t2.code = '002'
where t1.code = '001'

union all

select t2.date
, null
, t2.value
from `test` as t1
right outer
join `test` as t2
on t1.date
= t2.date
and t1.code = '001'
where t2.code = '002'
and t1.date is null

you'll need mysql 4.0 to support union, and if you don't have any code 002 rows without a code 001 row, then you can get away with just the first of the two queries|||ah, the good old denormalization questionI wondered what on earth you were babbling about as I read your response, but when I went back and re-read the question carefully I discovered that you were correct! I've always preferred the structure:SELECT date
, Sum(CASE WHEN '001' = code THEN value END) AS '001'
, Sum(CASE WHEN '002' = code THEN value END) AS '002'
, Sum(CASE WHEN '003' = code THEN value END) AS '003'
FROM test
GROUP BY dateThis only has to make one pass through the table, so it can be more efficeient if the optimiser does its job.

-PatP|||i babble often

i'm wrong occasionally

:cool:|||Thank you all very much !! :)
Paolo Saudin

[multivalued DBMS] How can I make this SELECT?

Hi everybody!

This is my first post on this board, so excuse me for my poor english and if I'm not respecting any rules here.

I have a problem to make a query that seems simple tough.

There is only one table:

Products:
id (int) 1:1 unique and indexed
name (string) 1:1
manufacturers (int) 1:n multivalued

I want to select the name of all the products that have the most manufacturers.

I think the query should be like this:

SELECT P.name FROM Products AS P
WHERE count(P.manufacturers) = MAX(count(P.manufacturers))

But I don't have any multivalued DBMS to test this and I just need the syntax to make an algorithm that will translate it to a non multivalued DBMS

Thanks for helping me.there might be something in sql 2003 but i doubt it

the problem is that your one-to-many multivalues list of id numbers fails first normal form

sql is not designed to accommodate such failings easily

redesign your database, and then it's easy to get what you want

rather than multiple id values in one field, you want multiple rows in a related table

rudy|||I think this is what you are after (you need to do a subquery to get the max):
SELECT P.name FROM Products AS P
WHERE P.manufacturers = (SELECT MAX(manufacturers) FROM Products)|||Thanks for responding

I agree that this design for DB is not a good way according usual way, so I'm gonna give a little more explainations:

I'm working on a conceptual DBMS, it should be a perfect DBMS that allows everything that should be possible in SQL, TSQL and OSQL.

This DBMS doens't exist, of course, but is used to used 4 differents geographical Databases that have their own limitations. This DBMS is thus an interface for the existing DBMS an some others that we sould add in the future.

It makes the scientis able to imagine the DB they need without any limitation. Then the programms will create a template for a classical relational DB and will translate all the query in it.

So, that's why I need to know how this kind of query should be done, in order to make something that wil translate it.

Hope i've been clear enough,
regards.|||Originally posted by BruceG
I think this is what you are after (you need to do a subquery to get the max):
SELECT P.name FROM Products AS P
WHERE P.manufacturers = (SELECT MAX(manufacturers) FROM Products)

thanks, but this isn't. I don't need to know the highest id of manufacturers, but the number of manufacturers in this field.|||> It makes the scientist able to imagine the DB
> they need without any limitation.

use a data model diagram to communicate your database design

a good tool like ERwin can then generate the DDL for any database system you choose

rudy|||If I could, I would...

But I'm not the author of this project, just a little student doing his end studies work...|||Sorry, I misinterpreted the scenario. Can you supply a sample set of values, along with the results you would expect?|||Sure, here is an example:

Products:
[1][puppet][1, 3, 5]
[2][ballon][2]
[3][teddy bear][1, 2, 4]
[4][puzzle][3, 6]

(separator are here just for the example)

The result should be
[puppet]
[teddy bear]

because all of them have 3 manufacturers and that it's the maximum found in the table.

Hope this help.|||Originally posted by le mogwai
Sure, here is an example:

Products:
[1][puppet][1, 3, 5]
[2][ballon][2]
[3][teddy bear][1, 2, 4]
[4][puzzle][3, 6]

(separator are here just for the example)

The result should be
[puppet]
[teddy bear]

because all of them have 3 manufacturers and that it's the maximum found in the table.

Hope this help.
OK, in a real database you need 3 tables:

create table products
( product_id number primary key
, product_name varchar2(30)
);

create table manufacturers
( manuf_id number primary key
, manuf_name varchar2(30)
);

create table product_manufacturers
( product_id number references products
, manuf_id number references manufacturers
, primary key (product_id,manuf_id)
);

These are populated with your sample data like this:

select * from products;

PRODUCT_ID PRODUCT_NAME
---- ----------
1 puppet
2 balloon
3 teddy bear
4 puzzle

select * from manufacturers;

MANUF_ID MANUF_NAME
---- ----------
1 1
2 2
3 3
4 4
5 5
6 6

select * from product_manufacturers;

PRODUCT_ID MANUF_ID
---- ----
1 1
1 3
1 5
2 2
3 1
3 2
3 4
4 3
4 6

The following query, which works in Oracle at least, gets the result you seek:

select p.product_name
from products p, product_manufacturers pm
where p.product_id = pm.product_id
group by p.product_name
having count(*) =
( select max(cnt) from
( select product_id, count(*) cnt
from product_manufacturers
group by product_id
)
);

PRODUCT_NAME
----------
puppet
teddy bear|||thanks for your response, but i know that already...

I know how this should be in a "real" DB, but what i need is how it could be in this kind of DB

I know Oracle can support this kind of table by using Varray or nested tables since version 8, but I don't have this DB to make my test.

It seems that you have this one, it would help me a lot if you could make this little test.

Thanks a lot.|||Originally posted by le mogwai
thanks for your response, but i know that already...

I know how this should be in a "real" DB, but what i need is how it could be in this kind of DB

I know Oracle can support this kind of table by using Varray or nested tables since version 8, but I don't have this DB to make my test.

It seems that you have this one, it would help me a lot if you could make this little test.

Thanks a lot.
Oh, if you insist. I LOATHE using nested tables, I mean - what is the ADVANTAGE? I can see the disadvantages all right...:)

SQL> create type manuf_t is table of number;
2 /

Type created.

SQL> create table products
2 ( product_id number primary key
3 , product_name varchar2(30)
4 , manufacturers manuf_t
5 ) nested table manufacturers store as product_manufacturers;

Table created.

SQL> insert into products values (1,'puppet',manuf_t(1,3,5));

1 row created.

SQL> insert into products values (2,'balloon',manuf_t(2));

1 row created.

SQL> insert into products values (3,'teddy bear',manuf_t(1,2,4));

1 row created.

SQL> insert into products values (4,'puzzle',manuf_t(3,6));

1 row created.

SQL> select product_name
2 from products p, table(p.manufacturers) pm
3 group by p.product_name
4 having count(*) =
5 ( select max(cnt) from
6 ( select count(*) cnt
7 from products p, table(p.manufacturers) pm
8 group by p.product_id
9 )
10* );

PRODUCT_NAME
----------
puppet
teddy bear

Using VARRAY the identical code above works, just change the TYPE definition to:

SQL> create type manuf_t as varray(10) of number;

This limits you to a maximum number of manufacturers per product (e.g. 10 in my example).|||This will be tough in straight SQL because the comma-delimited string of manufacturers would have to be parsed to count how many commas there are in the string; I don't know of a SQL function that can do that.

Assuming the manufacturer IDs are all roughly the same length, you could do something like:
SELECT P.name FROM Products AS P
WHERE LENGTH(P.manufacturers) = (SELECT MAX(LENGTH(manufacturers)) FROM Products)|||bruce, tony, you guys are way too nice

the solution is to read the table sequentially with PHP

i mean, isn't that where the concept of a comma-delimited list being good design seems to be coming from nowadays?

;)|||Originally posted by andrewst
Oh, if you insist. I LOATHE using nested tables, I mean - what is the ADVANTAGE? I can see the disadvantages all right...:)

SQL> create type manuf_t is table of number;
2 /

Type created.

SQL> create table products
2 ( product_id number primary key
3 , product_name varchar2(30)
4 , manufacturers manuf_t
5 ) nested table manufacturers store as product_manufacturers;

Table created.

SQL> insert into products values (1,'puppet',manuf_t(1,3,5));

1 row created.

SQL> insert into products values (2,'balloon',manuf_t(2));

1 row created.

SQL> insert into products values (3,'teddy bear',manuf_t(1,2,4));

1 row created.

SQL> insert into products values (4,'puzzle',manuf_t(3,6));

1 row created.

SQL> select product_name
2 from products p, table(p.manufacturers) pm
3 group by p.product_name
4 having count(*) =
5 ( select max(cnt) from
6 ( select count(*) cnt
7 from products p, table(p.manufacturers) pm
8 group by p.product_id
9 )
10* );

PRODUCT_NAME
----------
puppet
teddy bear

Using VARRAY the identical code above works, just change the TYPE definition to:

SQL> create type manuf_t as varray(10) of number;

This limits you to a maximum number of manufacturers per product (e.g. 10 in my example).

GREAT! You're my god! :D

In fact, I'm in accord with you. I don't find any advantage of this use of DB, but I have no choice for doing it.

Well, one more time, thanks to all of you for your help and your patience.|||Originally posted by r937
bruce, tony, you guys are way too nice

the solution is to read the table sequentially with PHP

i mean, isn't that where the concept of a comma-delimited list being good design seems to be coming from nowadays?

;)
So true. And Nested tables are like comma-delimited lists on steroids! The trouble is, since they are one of the new OO features in Oracle, all rookie database designers think they should be using them rather than out-moded relational tables. Worse, they are encouraged/forced to use them by college courses that teach features rather than theory.

[Microsoft][ODBC SQL Server Driver]Timeout expired

I keep getting the above error when i run a sql from my web page. The query runs fine in enterprise manager. What is worng . Please help.

sql="SELECT top 1 * FROM V_ISSUE WHERE ISSUE_ID=" & issueid & " order by issue_id"

if Session("rs").State = 1 then Session("rs").Close
Session("rs").Open sql,Session("MyConn")Please Try this Code

sql="SELECT top 1 * FROM V_ISSUE WHERE ISSUE_ID=" & issueid & " order by issue_id"

Session("rs").Open sql,Session("MyConn")

Session("rs").close
Set Session("rs")=Nothing

[How do I...?] SELECT from a specific partition?

If I have a table split up among partitions A, B, C, how do I select only from partition C?
~LeTo do that you would normally know the partition function definition, and use the values that you know would be stored in the corresponding partition.

[CR6] Can I use "SELECT FROM WHERE"?

Hi, sorry for my english :-\

I'm working on Crystal Report 6.0
I have 2 tables that are not in join, but I need a field with a "where" condition.
Example:
TableA:
fldDescription

TableB:
fldCode
fldDesc

I have a formula field which should show TableB.fldDesc, using a code in TableA.fldDescription
I extract 2 char in TableA.fldDescription:
strMyField = Trim (Mid ({TableA.fldDescription},2 , 2));

Now I must show TableB.fldDesc where TableB.fldCode = strMyField.
How can I do this? I have no join between this tables, I tried to put in the formula field "Select TableB.fldDesc where TableB.fldCode = strMyField" but it doesn't work.

Could you help me, please?How are TableA and TableB related? Do you even need any data from TableA?

"Select TableB.fldDesc where TableB.fldCode = strMyField"

If this were VB, you would have to do it like this:

"Select TableB.fldDesc where TableB.fldCode = '" & strMyField & "'"

In other words, you would have to concatenate the variable into the string you're using for your select statement. Also, you would need to put the string in single quotes (as shown in Red) to tell your database that it's a string. (These examples are derived from VB 6 and SQL Server 7, so you may need to adjust them accordingly).

I don't use Crystal Reports for direct database access. I let VB do all the data gathering and I just pass the data itself to Crystal for displaying. I'm not sure what the correct syntax is for Database SQL Statement within Crystal Reports, but I hope that my examples might give you a starting point to figure out your problem.

Good Luck!

[ask] how to append string to the previous select statement bounded to gridview

i have a case :

i'm using a select statement with SqlCommand and save the results in the SQLDataAdapter

and using the data table I post the result to the gridview and show it there...

my question is what should I do to append string to the SQL Command??

I'm not sure if I understand what you're asking. Can you use something like this

SqlCommand.CommandText &=" appended text "

before executing the command?

|||

yes i need the command text to be appended with some text later

thanks anyway, i think it's better to try it first :)

@Variables

Just trying to confirm what I think I am seeing...
If I run a query which does something like this...
Select @.MyField = UserName
from dbo.signon
running this SQL statement inside a procedure does not produce anything in
terms of output row set? Just trying to confirm, that the client
application which is calling this procedure would not get any rows returned
(in this portion of the code) and that it is just setting some @. variables
for my later use within the Proc. Is that correct?
It is apparently "illegal" for me to have a statement like the following:
Select @.MyField = UserName, password
from dbo.signon
This is not a "real" situation, I am just trying to understand what I can
do and what I can not do. When I try this, I get the following error "A
SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.", which brings me to the conclusion that it
is illegal.
So in a situation where I needed to populate a "@.MyField" variable and also
retrieve the password (for some reason), this would be a two step process
(assuming no cursor logic)?
Thanks in advance for your assistance!!!!!!This is a multi-part message in MIME format.
--=_NextPart_000_00AC_01C3E587.E1CFBF20
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
No rows will be returned to the client when you use SELECT to populate
variables. You can populate multiple variables concurrently:
Select @.MyField = UserName
, @.MyFiled2 = password
from dbo.signon
Note: If your select would retrieve multiple rows, then the results are
unpredictable.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns947E5CB70CF08JimHeaveyhotmailcom@.207.46.248.16...
Just trying to confirm what I think I am seeing...
If I run a query which does something like this...
Select @.MyField = UserName
from dbo.signon
running this SQL statement inside a procedure does not produce anything in
terms of output row set? Just trying to confirm, that the client
application which is calling this procedure would not get any rows returned
(in this portion of the code) and that it is just setting some @. variables
for my later use within the Proc. Is that correct?
It is apparently "illegal" for me to have a statement like the following:
Select @.MyField = UserName, password
from dbo.signon
This is not a "real" situation, I am just trying to understand what I can
do and what I can not do. When I try this, I get the following error "A
SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.", which brings me to the conclusion that it
is illegal.
So in a situation where I needed to populate a "@.MyField" variable and also
retrieve the password (for some reason), this would be a two step process
(assuming no cursor logic)?
Thanks in advance for your assistance!!!!!!
--=_NextPart_000_00AC_01C3E587.E1CFBF20
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

No rows will be returned to the client =when you use SELECT to populate variables. You can populate multiple =variables concurrently:
Select @.MyField =3D UserName
, @.MyFiled2 =3D = password from dbo.signon
Note: If your select would retrieve multiple rows, then the results =are unpredictable.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jim Heavey" wrote =in message news:Xns9=47E5CB70CF08JimHeaveyhotmailcom@.207.46.248.16...Just trying to confirm what I think I am seeing...If I run a query =which does something like this... Select @.MyField ==3D UserName from dbo.signonrunning this SQL =statement inside a procedure does not produce anything in terms of output row set? Just trying to confirm, that the client application which =is calling this procedure would not get any rows returned (in this =portion of the code) and that it is just setting some @. variables for my later =use within the Proc. Is that correct?It is apparently ="illegal" for me to have a statement like the following: Select =@.MyField =3D UserName, password from =dbo.signonThis is not a "real" situation, I am just trying to understand what I can =do and what I can not do. When I try this, I get the following error "A SELECT statement that assigns a value to a variable must not be =combined with data-retrieval operations.", which brings me to the conclusion =that it is illegal.So in a situation where I needed to populate a ="@.MyField" variable and also retrieve the password (for some reason), this =would be a two step process (assuming no cursor logic)?Thanks in =advance for your assistance!!!!!!

---

--=_NextPart_000_00AC_01C3E587.E1CFBF20--

@Variables

Just trying to confirm what I think I am seeing...
If I run a query which does something like this...
Select @.MyField = UserName
from dbo.signon
running this SQL statement inside a procedure does not produce anything in
terms of output row set? Just trying to confirm, that the client
application which is calling this procedure would not get any rows returned
(in this portion of the code) and that it is just setting some @. variables
for my later use within the Proc. Is that correct?
It is apparently "illegal" for me to have a statement like the following:
Select @.MyField = UserName, password
from dbo.signon
This is not a "real" situation, I am just trying to understand what I can
do and what I can not do. When I try this, I get the following error "A
SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.", which brings me to the conclusion that it
is illegal.
So in a situation where I needed to populate a "@.MyField" variable and also
retrieve the password (for some reason), this would be a two step process
(assuming no cursor logic)?
Thanks in advance for your assistance!!!!!!No rows will be returned to the client when you use SELECT to populate
variables. You can populate multiple variables concurrently:
Select @.MyField = UserName
, @.MyFiled2 = password
from dbo.signon
Note: If your select would retrieve multiple rows, then the results are
unpredictable.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns947E5CB70CF08JimHeaveyhotmailcom
@.207.46.248.16...
Just trying to confirm what I think I am seeing...
If I run a query which does something like this...
Select @.MyField = UserName
from dbo.signon
running this SQL statement inside a procedure does not produce anything in
terms of output row set? Just trying to confirm, that the client
application which is calling this procedure would not get any rows returned
(in this portion of the code) and that it is just setting some @. variables
for my later use within the Proc. Is that correct?
It is apparently "illegal" for me to have a statement like the following:
Select @.MyField = UserName, password
from dbo.signon
This is not a "real" situation, I am just trying to understand what I can
do and what I can not do. When I try this, I get the following error "A
SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.", which brings me to the conclusion that it
is illegal.
So in a situation where I needed to populate a "@.MyField" variable and also
retrieve the password (for some reason), this would be a two step process
(assuming no cursor logic)?
Thanks in advance for your assistance!!!!!!

@variable in SELECT ... WHERE ... IN clause

Is there a way to create a query that can be like:

DECLARE @.group_id_list varchar(100)

SET @.group_id_list='100,101,150'

SELECT * FROM abc WHERE abc_id IN (@.group_id_list)

I get the error "Syntax error converting the varchar value '100,101,150' to a column of data type int.

Do I need to resort to a dynamic SQL statement?

You do.|||Not if you don't want to. In many cases I prefer to use a udf that I've created that takes a comma-delimited varchar and returns a table. Then you can either join on the table to limit your results, or you can use IN (SELECT id FROM Split(@.param,DEFAULT) alias1) in your where clause. The second param into my Split UDF is what the separator is (default is comma).|||

Thank you. I actually ended up doing something similar (never thought of using a UDF):

DECLARE @.groups TABLE (group_id int)

I ran a while loop inserting the values into the @.groups table, then used:

SELECT * FROM abc WHERE abc_id IN (SELECT group_id FROM @.groups)

I don't know if it's efficient, but my dynamic SQL statement was going to exceed 14k in length!

@NUM_MEMBERS = EXEC (USE +@X + ; SELECT COUNT(Member_Name) FROM Logins) -HOW?

DECLARE @.X VARCHAR(10)
DECLARE @.NUM_MEMBERS SMALLINT
SELECT @.X = 'other_db'

@.NUM_MEMBERS = EXEC ('USE ' +@.X + '; SELECT COUNT(Member_Name) FROM Logins')

I get an error when I try to store the result in @.NUM_MEMBERS. :/ I've been looking all over for the correct syntax. Can anyone help?

ERROR:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@.NUM_MEMBERS'.

thx n advits probably better just to use sp_executesql, eh?|||I think you have to to get results back in variables from a dynamic SQL string.

@local variable (newbie)

I am trying to run a query.

Declare ColNames_Csr Cursor
-- Open Cursor that contains all column names
for
Select Column_Name from Information_Schema.columns where Table_Name = @.TableName

Open ColNames_csr

Fetch Next From ColNames_csr into @.FieldName

While ...

Begin
.
.
.
Select @.FieldValue = (Select @.FieldName from Contacts)
Print @.FieldValue
.
.
.
end

@.SomeValue always returns the FieldName (EMail) not @.FieldName (name@.somewhere.com).

I am trying to roll through all the columns and see what the value is for @.fieldname

What am i missing?

Thanks
LJOriginally posted by LittleJonny
I am trying to run a query.

Declare ColNames_Csr Cursor
-- Open Cursor that contains all column names
for
Select Column_Name from Information_Schema.columns where Table_Name = @.TableName

Open ColNames_csr

Fetch Next From ColNames_csr into @.FieldName

While ...

Begin
.
.
.
Select @.FieldValue = (Select @.FieldName from Contacts)
Print @.FieldValue
.
.
.
end

@.SomeValue always returns the FieldName (EMail) not @.FieldName (name@.somewhere.com).

I am trying to roll through all the columns and see what the value is for @.fieldname

What am i missing?

Thanks
LJ

Query below returns value of @.FieldName - no field in table. Think about dynamic query ...

(Select @.FieldName from Contacts)|||Dynamic Query? Im not sure I understand.

@.FieldName = 'Name'

Select @.FieldValue = (Select @.FieldName from Contacts where contactid = 32)

Print @.FieldValue

Always Prinst "Name"

How do I get the data in Contacts.@.FieldName?

Thanks
LJ|||Originally posted by LittleJonny
Dynamic Query? Im not sure I understand.

@.FieldName = 'Name'

Select @.FieldValue = (Select @.FieldName from Contacts where contactid = 32)

Print @.FieldValue

Always Prinst "Name"

How do I get the data in Contacts.@.FieldName?

Thanks
LJ

Dynamic query is something like this:

create table test(id int, code varchar(10))
go
insert test values(1,'A')
insert test values(2,'B')
insert test values(3,'C')
go
create proc retvalue(@.sql varchar(8000),@.result varchar(50) output)
as
declare @.res varchar(50)
create table #tmp(res varchar(50))
insert #tmp exec(@.sql)
select @.result=res from #tmp
return
go
declare @.res varchar(50),@.sql varchar(8000),@.field varchar(50)
set @.field='code'
set @.sql='select '+@.field+' from test'
exec retvalue @.sql,@.res output
select @.res