473,324 Members | 2,166 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,324 software developers and data experts.

A complex SELECT in two phases

I´m trying a select clause in two steps because it´s too complex.

First:

SELECT Reference, Results.idEnsayo, Results.Num_taladro, min(Results.dTime)
+ 500 AS tIni, max(Results.dTime) - 500 AS tLast
FROM Results INNER JOIN Ensayos ON Results.idEnsayo=Ensayos.idEnsayo
WHERE (Ensayos.Reference=9)
GROUP BY Reference, Results.idEnsayo, Num_Taladro;

This consult fills table called "resTable01" and then

Second (using the previous):

SELECT idEnsayo, Num_taladro, Avg(Dat1) as avgDat1, Avg(Dat2) as avgDat2,
Min(Dat1) as minDat1, Min(Dat2) as minDat2
FROM Results INNER JOIN resTable01 ON (Results.idEnsayo=resTable01.idEnsayo
AND Results.Num_taladro=resTable01.Num_taladro)
WHERE (Results.dTime Between resTable01.tIni and resTable01.tLast)
GROUP BY Results.idEnsayo, Results.Num_Taladro;

Actually the Select is more complex but it shows the problem

I´m trying it using ADO.NET with a DataSet (the database is ACCESS).
I fill it with the first command and i would like to use the table
(DataSet.Table[0]) to make the new command.
This is my problem, is it possible? There is another way to do that (a
nested select)?.

Thanks in advance

Jun 4 '07 #1
6 1735
Jon,

No, there isn't. The data table that you have is in memory, and no
longer exists on Sql Server (in terms of the result set for that statement).
You will have to issue one statement. If you are using Sql Server 2005,
then you can use common table expressions:

with resTable01 as
(
select
Reference, Results.idEnsayo, Results.Num_taladro, min(Results.dTime)
+ 500 AS tIni,
max(Results.dTime) - 500 AS tLast
FROM
Results
INNER JOIN Ensayos ON Results.idEnsayo=Ensayos.idEnsayo
WHERE
Ensayos.Reference = 9
GROUP BY
Reference, Results.idEnsayo, Num_Taladro
)
SELECT
idEnsayo, Num_taladro, Avg(Dat1) as avgDat1, Avg(Dat2) as avgDat2,
Min(Dat1) as minDat1, Min(Dat2) as minDat2
FROM
Results
INNER JOIN resTable01 ON
Results.idEnsayo=resTable01.idEnsayo AND
Results.Num_taladro=resTable01.Num_taladro
WHERE
Results.dTime Between resTable01.tIni and resTable01.tLast
GROUP BY
Results.idEnsayo, Results.Num_Taladro;

If you are using a version prior to Sql Server 2005, then you can take
the part in the "with" statement and place it in a local table variable (in
a stored procedure), and then access that variable in the next statement in
the procedure.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Jon Bilbao" <jo*******@trilonet.comwrote in message
news:ek**************@TK2MSFTNGP02.phx.gbl...
I´m trying a select clause in two steps because it´s too complex.

First:

SELECT Reference, Results.idEnsayo, Results.Num_taladro,
min(Results.dTime) + 500 AS tIni, max(Results.dTime) - 500 AS tLast
FROM Results INNER JOIN Ensayos ON Results.idEnsayo=Ensayos.idEnsayo
WHERE (Ensayos.Reference=9)
GROUP BY Reference, Results.idEnsayo, Num_Taladro;

This consult fills table called "resTable01" and then

Second (using the previous):

SELECT idEnsayo, Num_taladro, Avg(Dat1) as avgDat1, Avg(Dat2) as avgDat2,
Min(Dat1) as minDat1, Min(Dat2) as minDat2
FROM Results INNER JOIN resTable01 ON
(Results.idEnsayo=resTable01.idEnsayo AND
Results.Num_taladro=resTable01.Num_taladro)
WHERE (Results.dTime Between resTable01.tIni and resTable01.tLast)
GROUP BY Results.idEnsayo, Results.Num_Taladro;

Actually the Select is more complex but it shows the problem

I´m trying it using ADO.NET with a DataSet (the database is ACCESS).
I fill it with the first command and i would like to use the table
(DataSet.Table[0]) to make the new command.
This is my problem, is it possible? There is another way to do that (a
nested select)?.

Thanks in advance


Jun 4 '07 #2
Thanks Nicholas for your interest

Your answer is appreciated but actually I´m using Access Database so I can´t
use the with clause nor create local variables.

I was looking for something like a cross selection between DataSet in memory
and the BD but I see that it is a wrong way.

Jun 4 '07 #3
The following might work then:

SELECT
idEnsayo, Num_taladro, Avg(Dat1) as avgDat1, Avg(Dat2) as avgDat2,
Min(Dat1) as minDat1, Min(Dat2) as minDat2
FROM
Results
INNER JOIN
(
select
Reference, Results.idEnsayo, Results.Num_taladro,
min(Results.dTime) + 500 AS tIni, max(Results.dTime) - 500
AS tLast
FROM
Results
INNER JOIN Ensayos ON Results.idEnsayo=Ensayos.idEnsayo
WHERE
Ensayos.Reference = 9
GROUP BY
Reference, Results.idEnsayo, Num_Taladro
) as resTable01 ON
Results.idEnsayo=resTable01.idEnsayo AND
Results.Num_taladro=resTable01.Num_taladro
WHERE
Results.dTime Between resTable01.tIni and resTable01.tLast
GROUP BY
Results.idEnsayo, Results.Num_Taladro;

I don't know that access can handle that complex a query though. If
not, then you will have to get two separate result sets and the do the
joining yourself (which is a PITA).
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Jon Bilbao" <jo*******@trilonet.comwrote in message
news:OE**************@TK2MSFTNGP05.phx.gbl...
Thanks Nicholas for your interest

Your answer is appreciated but actually I´m using Access Database so I
can´t use the with clause nor create local variables.

I was looking for something like a cross selection between DataSet in
memory and the BD but I see that it is a wrong way.

Jun 4 '07 #4

This is my problem, is it possible? There is another way to do that (a
nested select)?.

Thanks in advance
I don't know if this will buy you anything but you can use the Shape command
with a MSAccess Data Provider, which you should be able to do in C#.

http://support.microsoft.com/kb/308045

Jun 4 '07 #5


"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.comwrote in
message news:eS**************@TK2MSFTNGP02.phx.gbl...
The following might work then:

SELECT
idEnsayo, Num_taladro, Avg(Dat1) as avgDat1, Avg(Dat2) as avgDat2,
Min(Dat1) as minDat1, Min(Dat2) as minDat2
FROM
Results
INNER JOIN
(
select
Reference, Results.idEnsayo, Results.Num_taladro,
min(Results.dTime) + 500 AS tIni, max(Results.dTime) -
500 AS tLast
FROM
Results
INNER JOIN Ensayos ON Results.idEnsayo=Ensayos.idEnsayo
WHERE
Ensayos.Reference = 9
GROUP BY
Reference, Results.idEnsayo, Num_Taladro
) as resTable01 ON
Results.idEnsayo=resTable01.idEnsayo AND
Results.Num_taladro=resTable01.Num_taladro
WHERE
Results.dTime Between resTable01.tIni and resTable01.tLast
GROUP BY
Results.idEnsayo, Results.Num_Taladro;

I don't know that access can handle that complex a query though. If
not, then you will have to get two separate result sets and the do the
joining yourself (which is a PITA).
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Jon Bilbao" <jo*******@trilonet.comwrote in message
news:OE**************@TK2MSFTNGP05.phx.gbl...
>Thanks Nicholas for your interest

Your answer is appreciated but actually I´m using Access Database so I
can´t use the with clause nor create local variables.

I was looking for something like a cross selection between DataSet in
memory and the BD but I see that it is a wrong way.

Using something like you wrote above, Nick, the following also works in SQL
Server 2k. I've been using it for awhile for complex selects.
select
MyTable.*
from (
select
1 as Value1,
2 as Value2
) MyTable

:)

HTH,
Mythran
Jun 4 '07 #6
Thanks, both answers were helpfull

Jun 5 '07 #7

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

Similar topics

37
by: Xah Lee | last post by:
A Moronicity of Guido van Rossum Xah Lee, 200509 On Guido van Rossum's website: http://www.artima.com/weblogs/viewpost.jsp?thread=98196 dated 20050826, he muses with the idea that he would...
17
by: Chris Travers | last post by:
Hi all; I just made an interesting discovery. Not sure if it is a good thing or not, and using it certainly breakes first normal form.... Not even sure if it really works. However, as I am...
5
by: Bob Stearns | last post by:
Is there an easy way (without duplication of the complex expression) to use the same complex expression in all three places? Will something like this work? WITH (SELECT t.*, <complex expr> AS...
5
by: Trail Monster | last post by:
Ok, I've been searching the net now for several days and can't find how to do this anywhere. Version: VS 2005 Professional Release, 2.0 Framework Background: I have a complex business object...
22
by: pbd22 | last post by:
hi. I am having probelms with an update statement. every time i run it, "every" row updates, not just the one(s) intended. so, here is what i have. i have tried this with both AND and OR and...
13
by: howa | last post by:
e.g. <div class="main"> <p>ssssss</p> <p>ssssss</p> <p>ssssss</p> <p>ssssss</p>
8
by: robtyketto | last post by:
Greetings, My XML schema defines a customer to be of type UK or Non-UK European making use of xsi:type. The difference between the complex UK and Non-UK European customer types is they have an...
2
by: phpnewbie26 | last post by:
I currently have two drop down menus where the second one is populated from the first one. My second drop down menu should be able to do multiple selection. I have searched online and found out how...
6
by: phpnewbie26 | last post by:
My current form has one multiple select drop down menu as well as few other drop down menus that are single select. Originally I had it so that the multiple select menu was first, but this created...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.