Conditionally select from cte

Question

Post reply

Conditionally select from cte

irobertson

Hall of Fame

Points: 3695
More actions
June 8, 2009 at 9:13 am

#132738

Hi,
Can I conditionally select from a cte? I'm not sure it's possible.
E.g.
declare @input_variable int
;with cte as (
select Col1
, Col2
, Col3
, Col4
from MyTable
)
if @input_variable = 1
begin
select sum(Col1) from cte
end
if @input_variable = 2
begin
select sum(Col2) from cte
end
etc...
Thanks, Iain

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

You cant do that. A cte's scope is a single statement.

Maybe a view would better suit you ?

Clear Sky SQL
My Blog[/url]

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

Maybe this?

with cte as (

select Col1

, Col2

, Col3

, Col4

from MyTable

)

select case when @input_variable = 1 then sum(Col1)

when @input_variable = 2 then sum(Col2)

end

from cte

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 3

irobertson (6/8/2009)
Hi,
Can I conditionally select from a cte? I'm not sure it's possible.
E.g.
declare @input_variable int
;with cte as (
select Col1
, Col2
, Col3
, Col4
from MyTable
)
if @input_variable = 1
begin
select sum(Col1) from cte
end
if @input_variable = 2
begin
select sum(Col2) from cte
end
etc...
Thanks, Iain

How about this ?

drop table MyTable

create table Mytable

(Col1 int, Col2 Int, COl3 int, Col4 int)

insert into MyTable

select 1,2,3,4 UNION ALL

select 1,2,3,4

declare @input_variable int

set @input_variable = 2

;with cte as (

select case when @input_variable = 1 then Col1

when @input_variable = 2 then Col2

when @input_variable = 3 then Col3

when @input_variable = 4 then Col4

end as Col

from MyTable

)

Select Sum(Col) from cte

Cheers,

J-F

irobertson Hall of Fame Points: 3695 More actions · Answer 4

Hi,

Sorry for the slow reply - been beavering away.

Just a short note to say that J-F's method of embedding the case statment within the cte definition worked a treat. Thanks, appreciated.

Iain

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 5

J-F Bergeron

SSCrazy Eights

Points: 8593

June 11, 2009 at 11:23 am

#1008168

Happy I could help Ian,

Have a great day,

Cheers,

J-F