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 6 1878
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
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
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
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,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
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$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
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
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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...
| |