April 29, 2008 at 8:31 am
was looking for a way to do a conditional union? The union is used within a view but depending on what criteria is passed to the view i would like to be able to have different statements unioned. following are a couple of examples that I am pretty sure do not work but can give some idea of what i am trying to accomplish.
ex1
create view view_a (id,col_a,col_b,col_c,col_d)
as
select id,col_a,'','','' from table_a
union
select id,'',col_b,'','' from table_b
union
CASE
when id = 1 then select id,'','',col_c,'' from table_c
else select id,'','','',col_d from table_d
ex2
create view view_a (id,col_a,col_b,col_c,col_d)
as
if(id=1)
BEGIN
select id,col_a,'','','' from table_a
union
select id,'',col_b,'','' from table_b
union
select id,'','',col_c,'' from table_c
END
ELSE
BEGIN
select id,col_a,'','','' from table_a
union
select id,'',col_b,'','' from table_b
union
select id,'','','',col_d from table_d
END
April 29, 2008 at 8:37 am
You can't do that type of conditional logic in views. Study up on stored procedures. 😀
______________________________________________________________________
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. SelburgApril 29, 2008 at 8:48 am
You can't create a view this way. Where are you getting the id from in the case statement? It can't be from the earlier selects. I think what you need is a stored procedure with dynamic sql or cte's, or a user defined function.
If you post what needs to be done and the desired result we will probably be able to give you a solution
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 29, 2008 at 9:00 am
Here is a link to another forum discussing parameterized views (which SQL Server does not have) which applies to what you want to do, I think.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73034
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 29, 2008 at 9:00 am
April 29, 2008 at 11:22 am
I think that the closest that you could get to this would be an in-line table-valued function. Instead of using IF .. BEGIN.. SELECT.., you would put the IF conditions on individual WHERE clauses attached to each SELECT that would null out when you wanted to exclude that branch of the UNIONS.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 29, 2008 at 1:24 pm
thanks rberry, thats an interesting option except i dont have the luxury of changing the behavior of the application from using a view. Personaly, it gives me more insight on how to accomplish this (if needed) in the future
April 30, 2008 at 1:32 pm
Mind you, it would not necessarily perform very well.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply