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