Prev: creating a start and end date table
Next: Ranges in query
From: simon on 17 Feb 2010 09:57
I have comon table expression and I would like to select 2 different
recordsets:
;WITH transports AS
(
SELECT o.transportID, o.izd_id, quantity, valueQ, ROW_NUMBER()
OVER (ORDER BY izd_id DESC)AS row
FROM @transportID t INNER JOIN dbo.transportOrder o ON
t.transportID=o.transportID
)
SELECT *
FROM transports
WHERE row BETWEEN 50 AND 60;
SELECT COUNT(*) , sum(quantity), sum(valueQ),..
FROM transports t INNER JOIN ....

Is it possible or I must use temp table?

Thank you for your answer,
Simon
From: Plamen Ratchev on 17 Feb 2010 10:03
A CTE can be consumed only by one query, so you have to repeat the CTE in a second query or materialize the CTE result
set to a table.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Sheldon on 17 Feb 2010 14:12
This may prove to be a helpful article:

http://www.4guysfromrolla.com/webtech/071906-1.shtml
--
Sheldon


"simon" wrote:

> I have comon table expression and I would like to select 2 different
> recordsets:
> ;WITH transports AS
> (
> SELECT o.transportID, o.izd_id, quantity, valueQ, ROW_NUMBER()
> OVER (ORDER BY izd_id DESC)AS row
> FROM @transportID t INNER JOIN dbo.transportOrder o ON
> t.transportID=o.transportID
> )
> SELECT *
> FROM transports
> WHERE row BETWEEN 50 AND 60;
> SELECT COUNT(*) , sum(quantity), sum(valueQ),..
> FROM transports t INNER JOIN ....
>
> Is it possible or I must use temp table?
>
> Thank you for your answer,
> Simon
> .
>
From: --CELKO-- on 17 Feb 2010 17:25
>> I have common table expression and I would like to select 2 different recordsets: .. Is it possible or I must use temp table? <<

No, a CTE is like a derived table; it is part of the SELECT.
A temp table probably a bad idea. If you use the table a lot, then
put it in a VIEW. The VIEW will always be up to date. Or use cut&paste
and put the table expression where you need it.
From: simon on 18 Feb 2010 04:19
On 17 feb., 23:25, --CELKO-- <jcelko...(a)earthlink.net> wrote:
> >> I have common table expression and I would like to select 2 different recordsets:  .. Is it possible or I must use temp table? <<
>
> No, a CTE is like a derived table; it is part of the SELECT.
> A temp table probably a bad idea.  If you use the table a lot, then
> put it in a VIEW. The VIEW will always be up to date. Or use cut&paste
> and put the table expression where you need it.

Thank you for your answer. I can't put it into the view, because I
have @transportID in select, which is a table variable. Or is
something new in SQL2008 what I don't know.
Select is very complicated(not like this in example) and it executes
200ms and that is why I don't wont to execute it 2 times. If I create
view somehow, it would execute 2 times and the time would be 400ms.
I think it is faster to execute it once and insert it into temp
table(or table variable) and than read results from temp table as many
times as I need - in my case it is 2 times.
The execution time is the same if I insert it into table variable
instead of temp table - so I have #temp table, because I use SELECT
INTO.

As I know if you use CTE, SQL in behind also create some type of temp
table or variable, doesn't it? So it shouldn't be so different.

;WITH transports AS
(
SELECT o.transportID, o.izd_id, quantity, valueQ, ROW_NUMBER()
OVER (ORDER BY izd_id DESC)AS row
FROM @transportID t INNER JOIN dbo.transportOrder o ON
t.transportID=o.transportID
)

Any other suggestion? Thank you for your help

Best regards,
Simon
 |  Next  |  Last
Pages: 1 2
Prev: creating a start and end date table
Next: Ranges in query