November 30, 2004 at 7:55 am
I would like to be able to dynamically set the from table in my select statement. Following is an example of what I would like to do; I know this syntax will not work.
Depending on the value of View1.SomeCount I want to join to 1 of 4 additional views. I know I can do this through a sp which could build a string then executing it to get the results I would like. But, I would like to be able to create a new view based of this structure. I have tried many different avenues but haven't figured out a way to make this work, thus I'm asking if any of you have stumbled across such a problem and come up with a viable solution.
select *
from View1
join case View1.SomeCount
WHEN 1 then New_View1
WHEN 2 then New_View2
WHEN 3 then New_View3
ELSE
New_View4
end as New_View
on View1.nViewId = New_View.nViewId
where View1.nView1Id = 7
November 30, 2004 at 8:24 am
Hi,
Sorry, don't get the full idea of what you are trying to achieve with this - could you please expand a little further.....
Steve
We need men who can dream of things that never were.
November 30, 2004 at 8:32 am
I don't think it's possible. I have come across some similar query with a udf that wasn't allowed by MS design (and for good reason).
I think you may have to do something like this
Select * from view1 inner join new_view1 on x=y where SomeCount = 1
UNION [all]
Select * from view1 inner join new_view2 on x=y where SomeCount = 2
UNION [all]
...
November 30, 2004 at 9:08 am
You need an IF statement.
If x = 1
select y, z from my table
else
select y, z, from table2
You cannot change the from statement with a case.
November 30, 2004 at 9:12 am
I don't think an if is the answer here (because he seems to want to join on a value from the main view.. so it doesn't seem to be a static parameter from an sp).
But if it's the case then it's definitely doable, however, if I understood the question right then he'll have to go with union(s).
November 30, 2004 at 9:50 am
What I am trying to accomplish is to a different view based on the value from the main view which is not static.
What actually is going on here are - is I created a view to flattened out the data being returned. All 4 views have the same columns. But view1 - will have 1 associated data set therefore the view will only set the values of view1 attributes accordingly and attibutes of 2-4 to nulls. View2 would set values for 1 & 2 and null for 3 & 4, view 3 would set the values for 1,2, & 3 and null 4, view 4 sets the values for 1,2,3,4 -- hopefully you get the picture. I'm trying to use the dynicamic where because of performance. 90% of the views will fall into view1, 5% into view2, 3.5% into view3, and 1.5% into view4.
I thought I had figured a way around this by setting a stored procedure and calling it from a view by setting up a linked server back to myself and using openQuery() -- which works execpt one can not pass parameters through OpenQuery() becasue it expects a literal string.
November 30, 2004 at 10:49 am
It's on the same line as I thaught. I would try my first solution and if it doesn't work then I would suggest you post the data definition of the views/table and some sample data with the expected results. And I'll gladly help some more.
December 2, 2004 at 11:05 am
Joins, conditional they are not!
LEFT OUTER JOINs, you will use. LEFT OUTER JOIN to all tables in questions in 1 single SELECT.
A CASE expression then, you will use, to return columns desired from tables. Based on conditions passed, hmmm?
If different joins are required, or different structure of results returned, then separate stored procs they should be. A jedi will not overcomplicate by trying too much in 1 procedure. Try it not!
Yoda questions your database design, however -- show us table structure, you will! Poor design, it leads to the dark side.
Multiple tables for particular values of the same entity type, hmm? A jedi craves not that situation.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply