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 GWHy dont 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" <dcgringo@.visiontechnology.net> schrieb im Newsbeitrag
news:e9nOtz%23TFHA.580@.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" <dcgringo@.visiontechnology.net> wrote in message
news:e9nOtz%23TFHA.580@.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_ar..._procedures.htm

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

"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:e9nOtz%23TFHA.580@.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" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uTm7J%23%23TFHA.952@.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_ar..._procedures.htm
> It may give you a few ideas on how to deal with this.
> "DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
> news:e9nOtz%23TFHA.580@.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" <dcgringo@.visiontechnology.net> wrote in message
news:e9nOtz%23TFHA.580@.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

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 GWHy dont 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" <dcgringo@.visiontechnology.net> schrieb im Newsbeitrag
news:e9nOtz%23TFHA.580@.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" <dcgringo@.visiontechnology.net> wrote in message
news:e9nOtz%23TFHA.580@.TK2MSFTNGP15.phx.gbl...
>I have a series of select queries that use the nesting method but are creat
ing 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 a
nother 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 l
ist 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_ar..._procedures.htm
It may give you a few ideas on how to deal with this.
"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:e9nOtz%23TFHA.580@.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 sectio
n
> 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" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uTm7J%23%23TFHA.952@.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_ar..._procedures.htm
> It may give you a few ideas on how to deal with this.
> "DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
> news:e9nOtz%23TFHA.580@.TK2MSFTNGP15.phx.gbl...
>
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" <dcgringo@.visiontechnology.net> wrote in message
news:e9nOtz%23TFHA.580@.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
>
>

Switch Image

How do i switch an image after a user select/browse another image. I'm able to save the image back in the server database. but is not able to switch to the selected image. Please help...Could you please give us more information on what you are trying to do, and perhaps some code?

Ok, a user in the system which profile has been stored in the database including the path of his photo is viewing his particular. ie: name, address, phone number, his picture, etc. This user wish to change his current photo posted before. Thus, a HTML FileField is provided for him to browse the image and save it into our server. What i want to do is, when a user click on the Update button. The older picture shown in the page will be replaced with his newly given picture. The path of the picture stored in server is successfully updated. And I set the code to retrieve this user's profile including his picture from the database again after the Update button is clicked. I've tested the code by Updating picture and certain information, i noticed that all information can be updated and replacing the old information on the screen except that the picture did not change to the newly added picture in the database. Any idea...?
Try to add this to your page:
<meta http-equiv="Expires" content="0" />

Thanks PaaB. But it's not working. Where do i put that line anyway. I put it in between <HEAD> and </HEAD>. Am i right?

Yes it is the right place.
Can you show the source code.
When user chnege the image, is then changed path and name of the picture? I think about that when would be changed only path and not name of the picture then IE possibly use picture saved on client computer?

As i mentioned earlier. I have a HTML FileField where user can browse image and update it. the system will then saved the image into the server and update the directory stored in database. Finally, the system should display the latest image newly added by this user, but it is not. The path and directory of new image is updated in the database. same goes to the image saved in the server. I don't know if i'm right. if wrong, please correct me. i think that the system take the same image downloaded previously in temp folder. and if i'm right, is there any method that i can call client computer to download the image again? Please advise...Thank u

What object do you use for display picture - Image Web Control or Image Html Element? Do you update the ImageUrl/src parameter on the "uploading" postback?

I'm using Html element. After the new image has been saved on the server and its directory in the database. User can choose to view the same info again by a single click. Thus, i recall the sub that display the information once again. the code for image is like this.

myCommand =New SqlCommand("SELECT * FROM User_Profile WHERE ([User_ID] = '" & userid & "' AND [Company_ID] = '" & compid & "')", myConnection)

myReader = myCommand.ExecuteReader

myReader.Read()

imgUser.Src = myReader.Item("Photo")

thats it.. any mistake on this one?

This isn't related to your image issue, but you should be using Parameters with your SQL statement:

myCommand =NewSqlCommand("SELECT * FROM User_Profile WHERE ([User_ID] = @.user_id AND [Company_ID] = @.company_id ')",myConnection)
myCommand.Parameters.Add(New SqlParameter("@.user_id",SqlDbType.Integer)).Value = userid
myCommand.Parameters.Add(New SqlParameter("@.company_id",SqlDbType.Integer)).Value = compid

tried your suggestion, but it's not working. And even other information also is not displaying. i know the usage of parameters, usually i used it with stored procedure. there's nothing wrong with plain sql command compare with 1 that using parameter right?

nickless1 wrote:

imgUser.Src = myReader.Item("Photo")

You might try this to see if it makes a difference:

imgUser.Src = myReader.Item("Photo") & "?" & System.DateTime.Now().ToString()

Also, see this KB article:IIS: How to Disable Caching of Specific MIME Types

nickless1 wrote:

tried your suggestion, but it's not working. And even other informationalso is not displaying. i know the usage of parameters, usually i usedit with stored procedure. there's nothing wrong with plain sql commandcompare with 1 that using parameter right?

Sorry, I shouldn't have confused the issue. To protect yourself fromSQL injectionyou should always use parameters in SQL statements; you should neverconcatenate user-supplied data to a string to be executed. Ididn't meant to introduce another problem, however. Once you havesolved your image caching problem you can post another question aboutthe parameter problem.

Yes...It works. Thanks a lot. I couldn't open the page u gave. Would u mind to explain how it works? Thanks again...
I'm glad that approach worked for you. (And I've now corrected the link that I posted.)

Hi,
Im having kind of the same problem you have. Could you explain how you managed to solve it? as you might now, this issue is a brainbuster! :S

Switch page to SSL Programatically?

Howdy,
I'm trying to have select pages always be secure and others I don't care.
I'm using
If Request.ServerVariables("SERVER_PORT") = 80 Then
Dim strSecureURL
strSecureURL = "https://"
strSecureURL = strSecureURL & Request.ServerVariables("SERVER_NAME")
strSecureURL = strSecureURL & Request.ServerVariables("URL")
Response.Redirect(strSecureURL)
End If
This works, but is there a better way for .net 2.0?
Thanks,
Merry Christmas
David LozziInstead of testing for the server port variable, test for the HTTPS
variable. It will return ON if it's a secure connection or OFF it is isn't.
This would avoid any issues where the server or ssl certificate is running
on a port other than 80.
Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199...2006
"David Lozzi" <dlozzi@.nospam.nospam> wrote in message
news:%23VNNyktGHHA.4112@.TK2MSFTNGP04.phx.gbl...
> Howdy,
> I'm trying to have select pages always be secure and others I don't care.
> I'm using
> If Request.ServerVariables("SERVER_PORT") = 80 Then
> Dim strSecureURL
> strSecureURL = "https://"
> strSecureURL = strSecureURL & Request.ServerVariables("SERVER_NAME")
> strSecureURL = strSecureURL & Request.ServerVariables("URL")
> Response.Redirect(strSecureURL)
> End If
>
> This works, but is there a better way for .net 2.0?
>
> Thanks,
> Merry Christmas
> David Lozzi
>
"David Lozzi" <dlozzi@.nospam.nospam> wrote in message
news:%23VNNyktGHHA.4112@.TK2MSFTNGP04.phx.gbl...

> This works, but is there a better way for .net 2.0?
http://www.codeproject.com/aspnet/W...&select=1697847

Switch page to SSL Programatically?

Howdy,

I'm trying to have select pages always be secure and others I don't care.
I'm using

If Request.ServerVariables("SERVER_PORT") = 80 Then

Dim strSecureURL

strSecureURL = "https://"

strSecureURL = strSecureURL & Request.ServerVariables("SERVER_NAME")

strSecureURL = strSecureURL & Request.ServerVariables("URL")

Response.Redirect(strSecureURL)

End If

This works, but is there a better way for .net 2.0?

Thanks,

Merry Christmas

David LozziInstead of testing for the server port variable, test for the HTTPS
variable. It will return ON if it's a secure connection or OFF it is isn't.
This would avoid any issues where the server or ssl certificate is running
on a port other than 80.

--

Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199?-2006

"David Lozzi" <dlozzi@.nospam.nospamwrote in message
news:%23VNNyktGHHA.4112@.TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

Howdy,
>
I'm trying to have select pages always be secure and others I don't care.
I'm using
>
If Request.ServerVariables("SERVER_PORT") = 80 Then
>
Dim strSecureURL
>
strSecureURL = "https://"
>
strSecureURL = strSecureURL & Request.ServerVariables("SERVER_NAME")
>
strSecureURL = strSecureURL & Request.ServerVariables("URL")
>
Response.Redirect(strSecureURL)
>
End If
>
>
>
This works, but is there a better way for .net 2.0?
>
>
>
Thanks,
>
Merry Christmas
>
David Lozzi
>
>

"David Lozzi" <dlozzi@.nospam.nospamwrote in message
news:%23VNNyktGHHA.4112@.TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

This works, but is there a better way for .net 2.0?

http://www.codeproject.com/aspnet/W...&select=1697847