September 26, 2007 at 11:45 am
I have a script:
with problem(problem_name, kount)
as
(
select
problem_name = pr.name,
kount = count(*)
from
sdskticket t
join sdskproblem pr on t.problemid = pr.problemid
join person p on t.uuid = p.uuid
join teamMember tm on p.personid = tm.personid
join team te on tm.teamid = te.teamid
group by
pr.name
)
select
problem_name,
kount
from problem
select sum(kount)
from problem
However it displays a message that problem object name does not exist. If I comment one of the queries it returns perfectly. Does this mean that there is no way to re-use it ? I also tried to manipulate with ; but it does not help.
As the last resort I can use temp table but CTE solution would be nicer, I think this was one of the reasons for introducing it.
September 26, 2007 at 11:47 am
No, CTE's are only available to the statement immediately following them.
you will want to use a table variable instead.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 26, 2007 at 11:58 am
Mark Shvarts (9/26/2007)
I have a script:with problem(problem_name, kount)
as
(
select
problem_name = pr.name,
kount = count(*)
from
sdskticket t
join sdskproblem pr on t.problemid = pr.problemid
join person p on t.uuid = p.uuid
join teamMember tm on p.personid = tm.personid
join team te on tm.teamid = te.teamid
group by
pr.name
)
select
problem_name,
kount
from problem
select sum(kount)
from problem
However it displays a message that problem object name does not exist. If I comment one of the queries it returns perfectly. Does this mean that there is no way to re-use it ? I also tried to manipulate with ; but it does not help.
As the last resort I can use temp table but CTE solution would be nicer, I think this was one of the reasons for introducing it.
YES you can reuse it!
with problem(problem_name, kount)
as
(
select
problem_name = pr.name,
kount = count(*)
from
sdskticket t
join sdskproblem pr on t.problemid = pr.problemid
join person p on t.uuid = p.uuid
join teamMember tm on p.personid = tm.personid
join team te on tm.teamid = te.teamid
group by
pr.name
)
, Cte2 ( problem_name, kount )
AS
(
select
problem_name,
kount
from problem
)
select sum(kount)
from Cte2
Cheers!
* Noel
September 26, 2007 at 12:06 pm
Well, poo! I wasn't thinking of it that way. Of course, if you need info from both CTE's then you'll need to join them in the last select.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 26, 2007 at 12:15 pm
Hi Noel,
While your solution looks interesting, I copied it but I still get only one result set, while I need both.
If I try to add select * from problem I still get the same error.
September 26, 2007 at 12:21 pm
[p]WITH
problem (problem_name, kount)
AS (SELECT
problem_name = pr.name
,kount = count(*)
FROM
sdskticket t
LEFT JOIN sdskproblem pr
ON t.problemid = pr.problemid
LEFT JOIN person p
ON t.uuid = p.uuid
LEFT JOIN teamMember tm
ON p.personid = tm.personid
LEFT JOIN team te
ON tm.teamid = te.teamid
GROUP BY
pr.name)
,Cte2 (problem_name, kount)
AS
(SELECT
problem_name
,kount
FROM
problem)
SELECT
problem_name
,kount
,SUM(kount) AS totalKount
FROM
problem
CROSS JOIN Cte2[/p]
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 26, 2007 at 12:41 pm
Hi Jason, I understand what you mean. You append summary to the last column and it repeats itself as many times as many are records there.
Here I reproduce final select after fixing some minor errors:
select p.problem_name,
p.kount,
sum(c.kount) as totalKount
from problem p
cross join Cte2 c
group by
p.problem_name,
p.kount
However my goal is to get 2 separate result sets. Besides, the performance is suffering because of cross-join and multiple repetiotions of same value. For this purpose I would better use temp table or table variable, they are running faster.
September 26, 2007 at 12:45 pm
Yes, a table variable would in deed be faster.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 26, 2007 at 1:35 pm
Mark Shvarts (9/26/2007)
Hi Noel,While your solution looks interesting, I copied it but I still get only one result set, while I need both.
If I try to add select * from problem I still get the same error.
Mark for multiple resultsets you need a separated storage. You could create a VIEW and then run your two result sets from the view or as Jason suggested implement it with a table variable.
* Noel
September 26, 2007 at 1:56 pm
I am actually using temp table, but I thought that CTE would offer more elegant solution.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply