A complex SELECT in two phases

[Jon Bilbao]

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

 

[Nicholas Paldino [.NET/C# MVP]]

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]
- (e-mail address removed)

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

 

[Jon Bilbao]

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.

 

[Nicholas Paldino [.NET/C# MVP]]

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).

 

[Mr. Arnold]

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

 

[Mythran]

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]
- (e-mail address removed)

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

 

[Jon Bilbao]

Thanks, both answers were helpfull