substitute for nested select query

[DC Gringo]

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

 

[Jens Süßmeyer]

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.

 

[Tibor Karaszi]

Create a table with the values (one column, multiple rows) and do a subquery inside your IN (or
EXISTS).

 

[Raymond D'Anjou]

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_arrays_to_stored_procedures.htm

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

 

[Guest]

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]

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

 

[John Spiegel]

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