473,387 Members | 1,619 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

substitute for nested select query

I have a series of select queries that use the nesting method but are
creating such a huge query that the server can't handle it. The IN section
in some cases are so large that I can't even troubleshoot it in Query
Analyzer because it's larger than 64k. Is there another way to write:

SELECT t1.col1, t1.col2
FROM table1 t1
WHERE EXISTS
(
SELECT t2.col1, t2.col2
FROM table2 t2
WHERE t2.col2 IN ('1','2','3','4'....<there could be thousands of
list items here>
)

_____
DC G

Jul 21 '05 #1
6 1879
WHy don´t you store them in a table ? Would be much easier to maintain, you
ould additionaly activate / deactivate some entries here:

Create Tabel MyValues
(
MyValue varchar(200),
Acitvated bit
)
SELECT t1.col1, t1.col2
FROM table1 t1
WHERE EXISTS
(
SELECT t2.col1, t2.col2
FROM table2 t2
WHERE t2.col2 IN (Select * from myVales where Activated =1 )
HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

"DC Gringo" <dc******@visiontechnology.net> schrieb im Newsbeitrag
news:e9***************@TK2MSFTNGP15.phx.gbl...I have a series of select queries that use the nesting method but are
creating such a huge query that the server can't handle it. The IN section
in some cases are so large that I can't even troubleshoot it in Query
Analyzer because it's larger than 64k. Is there another way to write:

SELECT t1.col1, t1.col2
FROM table1 t1
WHERE EXISTS
(
SELECT t2.col1, t2.col2
FROM table2 t2
WHERE t2.col2 IN ('1','2','3','4'....<there could be thousands of
list items here>
)

_____
DC G

Jul 21 '05 #2
Create a table with the values (one column, multiple rows) and do a subquery inside your IN (or
EXISTS).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DC Gringo" <dc******@visiontechnology.net> wrote in message
news:e9***************@TK2MSFTNGP15.phx.gbl...
I have a series of select queries that use the nesting method but are creating such a huge query
that the server can't handle it. The IN section in some cases are so large that I can't even
troubleshoot it in Query Analyzer because it's larger than 64k. Is there another way to write:

SELECT t1.col1, t1.col2
FROM table1 t1
WHERE EXISTS
(
SELECT t2.col1, t2.col2
FROM table2 t2
WHERE t2.col2 IN ('1','2','3','4'....<there could be thousands of list items here>
)

_____
DC G

Jul 21 '05 #3
First of all, no need to post a question to all the groups in your
subscription list.
You should have posted only to microsoft.public.sqlserver.programming

How is the list being passed into the query?

Take a look at:
http://vyaskn.tripod.com/passing_arr...procedures.htm

It may give you a few ideas on how to deal with this.

"DC Gringo" <dc******@visiontechnology.net> wrote in message
news:e9***************@TK2MSFTNGP15.phx.gbl...
I have a series of select queries that use the nesting method but are
creating such a huge query that the server can't handle it. The IN section
in some cases are so large that I can't even troubleshoot it in Query
Analyzer because it's larger than 64k. Is there another way to write:

SELECT t1.col1, t1.col2
FROM table1 t1
WHERE EXISTS
(
SELECT t2.col1, t2.col2
FROM table2 t2
WHERE t2.col2 IN ('1','2','3','4'....<there could be thousands of
list items here>
)

_____
DC G

Jul 21 '05 #4
DC Gringo,

The example posted is not using a correlated subquery, the is not a relation
between table1 and table2. Idf it is correct, then you can rewrite it as:

if exists(select * from table2 where col2 in (...))
select col1, col2 from table1

if it was a typo error, then you can create a temporary table to insert the
values in the list, with an associated index and use:

create table #t (c1 int)

insert into #t values(...)
....
create nonclustered index ix_#t_c1 on #t(c1)

SELECT
t1.col1, t1.col2
FROM
table1 as t1
inner join
(
select
t2.col1
from
table2 as t2
inner join
#t
on #t.c1 = t2.col1
) as t3
on t1.col1 = t3.col1

drop table #t
go
AMB

"DC Gringo" wrote:
I have a series of select queries that use the nesting method but are
creating such a huge query that the server can't handle it. The IN section
in some cases are so large that I can't even troubleshoot it in Query
Analyzer because it's larger than 64k. Is there another way to write:

SELECT t1.col1, t1.col2
FROM table1 t1
WHERE EXISTS
(
SELECT t2.col1, t2.col2
FROM table2 t2
WHERE t2.col2 IN ('1','2','3','4'....<there could be thousands of
list items here>
)

_____
DC G

Jul 21 '05 #5
Raymond, frankly I didn't expect a SQL T-sql answer. I was expecting a
vb.NET answer...but figured I'd try both...I am passing it in via raw SQL
from a .vb component. It is a query building application.
_____
DC G
"Raymond D'Anjou" <rd*****@savantsoftNOSPAM.net> wrote in message
news:uT*****************@TK2MSFTNGP10.phx.gbl...
First of all, no need to post a question to all the groups in your
subscription list.
You should have posted only to microsoft.public.sqlserver.programming

How is the list being passed into the query?

Take a look at:
http://vyaskn.tripod.com/passing_arr...procedures.htm

It may give you a few ideas on how to deal with this.

"DC Gringo" <dc******@visiontechnology.net> wrote in message
news:e9***************@TK2MSFTNGP15.phx.gbl...
I have a series of select queries that use the nesting method but are
creating such a huge query that the server can't handle it. The IN
section in some cases are so large that I can't even troubleshoot it in
Query Analyzer because it's larger than 64k. Is there another way to
write:

SELECT t1.col1, t1.col2
FROM table1 t1
WHERE EXISTS
(
SELECT t2.col1, t2.col2
FROM table2 t2
WHERE t2.col2 IN ('1','2','3','4'....<there could be thousands of
list items here>
)

_____
DC G


Jul 21 '05 #6
Hey DC,

If you have rights to create tables temprorarily, I'd agree with most of the
other posts. A few other ideas follow. Also, can you post more specifics
about what sort of things can be part of the IN clause?

- Are there any patterns you can narrow pieces down to, e.g., CAST( t2.col2
AS int) BETWEEN 1 AND 42?
- Maybe you could use the query to narrow the majority of your data and
either manipulate it on the .NET side or use a DataView to handle the rest
of the trimming.
- Can the logic be "reversed", e.g., if you can have char representations of
numbers ranging '1' to '10000', use NOT IN and supply a smaller set of
non-valid options.

HTH,

John

"DC Gringo" <dc******@visiontechnology.net> wrote in message
news:e9***************@TK2MSFTNGP15.phx.gbl...
I have a series of select queries that use the nesting method but are
creating such a huge query that the server can't handle it. The IN section
in some cases are so large that I can't even troubleshoot it in Query
Analyzer because it's larger than 64k. Is there another way to write:

SELECT t1.col1, t1.col2
FROM table1 t1
WHERE EXISTS
(
SELECT t2.col1, t2.col2
FROM table2 t2
WHERE t2.col2 IN ('1','2','3','4'....<there could be thousands of
list items here>
)

_____
DC G

Jul 21 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: sarith sutha | last post by:
Hi Guys Need a help i am facing a sporadic issue when executing the query using nested table of numbers Here are the things which i did 1. I created a type as CREATE TYPE NUMBERTABLE AS...
3
by: WGW | last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost! everything I need. Maybe not efficiently, but usefully. However, I have a problem -- a complex query problem... I can...
12
by: Jeff Lanfield | last post by:
First of all, I apologize if coalescing is not the right term to describe my problem. I have a tree where each node has the same set of attributes (is the same entity) but child nodes should...
2
by: d2r2 | last post by:
Hi, I'm trying to run a nested (UNION) query against a MSAccessXP database (version 2002; build 10.6501.6714; SP3) In Access the SQL-statement executes just fine. When I run it in a asp-page I...
2
by: pk | last post by:
Let me start off by mentioning that I'm not a web developer by any means. I welcome any and all tips regarding code cleanliness and would love to hear about any conventions that I'm breaking. I...
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
6
by: DC Gringo | last post by:
I have a series of select queries that use the nesting method but are creating such a huge query that the server can't handle it. The IN section in some cases are so large that I can't even...
3
by: Frank Swarbrick | last post by:
I was just messing around trying to learn things and attempted the following: select brch_nbr , sum(case when post_flag != 'P' then amount else 0 end) as sum_amount from film.film_transactions...
9
by: P3Eddie | last post by:
Hello all! I don't know if this can even be done, but I'm sure you will either help or suggest another avenue to accomplish the same. My problem may be a simple find duplicates / do something...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.