January 27, 2011 at 5:47 am
Hi,
I have the following query. If you could help me to understand, how it will be evaluated, it would be helpful.
Select t1.col4, t1.col5, t2.col7
from table1 t1
inner join table2 t2
on (function(t1.col5)=1 and t1.col1=t2.col6)
or (function(t1.col5)=2 and t1.col1>t2.col6)
Note: Function returns a scalar value based on some condition.
is it like the system is going to evaluate first the the value of (function(t1.col5) then try to join or for each row it will evaluate the value and then join. just in case I have 2 different functions in my where clause, then what would happen?
Please let me know in case you need any further information.
January 27, 2011 at 11:19 am
Hi CELKO,
Thanks for such a descriptive answer. Really appreciate it. And I am really sorry, I can't post the exact code. Any ways, following are some more information that can help you to help me :-).
1) The function is a UDF. It basically pull the ids based in the values provided.
2) The joining condition is determined my the value returned by the function.
3) The function can return 30 possible values. So, the the joining condition can have 30 or conditions.
4) One more complexity is the columns in the joining condition can change everytime, like if the value returned by the function is 1 then the condition is t1.col1=t2.col2, if its 2 then t1.col2=t2.col2, if its 3 then t1.col3>200 and t1.col1=t2.col1 etc.
Hope I did not confuse you.
January 27, 2011 at 12:18 pm
1) The function is a UDF. It basically pull the ids based in the values provided.
It seems like this function can be eliminated and the lookup could be used directly as a joined table or a CTE as Joe already mentioned. Alternatively it could be changed into an inline-table-valued function.
As a side note: I don't think Joe can be confused that easily...;-)
January 27, 2011 at 5:01 pm
LutzM (1/27/2011)
@pravasis:1) The function is a UDF. It basically pull the ids based in the values provided.
It seems like this function can be eliminated and the lookup could be used directly as a joined table or a CTE as Joe already mentioned. Alternatively it could be changed into an inline-table-valued function.
As a side note: I don't think Joe can be confused that easily...;-)
Hahaha.
The problem is its very similar to the joining condition that we are trying to achieve i.e. the input values are repeated for some cases with different ids. There are some more columns which needs to be considered if the id is repeated. So, its not exactly a inner join with the main table.
January 27, 2011 at 5:48 pm
All right, let me tell you the simplified version of the requirement.
1) We need to pick value of a column as per the values of col1, col2, col2, col4, col5.
2) There are 400 probable values for the required column.
3) Output can be dependent on only col1, col1 and col2 , col1 and col3 or may be all the columns.
4) Lets discuss some of the conditions:
a)dependent on only col1 - if the col1 is 'a' then the result is 110. if its 'b' then 123.
b)dependent on col1 and col2 - if col1 is 'c' and col2 is >100 then result is 144. if col1 is 'd' and col2 is >600 then result is 145.
c) dependent on col1 and col3 - if col1 is 'c' and col3 'asdf' then result is 147. if col1 is 'd' and col3 is 'zxcv' then result is 149.
Similarly with each group the condition changes like - exact match, > some value, is numeric, sometimes not in a set of values etc. So all total we have 400 distinct conditions.
I dont think its advisable to write this in a case statement to achieve the desired result.
So, I thought of creating a table to store all the conditions and the desired output. But, I designed two tables. First one will help us to identify the exact rule that needs to be applied. Second one will help us to apply the exact rule and pick the expected value from the second table. Now you might have guessed what my function is doing and what for I am using those joining conditions.
Yes, you are right! The function helps us to decide the rule and then applying the joining condition I am picking the required value from the second table.
All total there are 50 condition groups (as discussed in item 4 a,b,c). And each of these group contain one or more input values and its corresponding output value(like we have 2 possible values in 4a). So basically my rules table stores the condition group information and the second table stores the exact requirements of the condition and the corresponding output value. Mostly col1 helps me to determine which rule to apply. But sometimes col2 is required along with col1. So, my rules table contain id, col1, col2 and description of the rule. At max I need to evaluate 4 conditions to figure out the exact rule then use the same in my joining condition.
Well, there is one more option to solve this problem. I can write a function which will perform all the operations and finally return me the output. But I think it would be slower than the approach mentioned above. Because, the system has to evaluate all the conditions to give the result. But in my case it has to evaluate only 4 conditions and then join the rule to pull the result set from the second table.
Hope its clear now!!!
January 28, 2011 at 11:15 am
Please provide table DDL and ready to use sample data as described in the first link in my signature. Also, please provide your expected output based on your sample data. You don't have to provide all conditions or columns. But enough so we can get the concept and to check if our solutions would work.
Your description is clear. I just don't think it will help much if we'd verbally describe the solution. And I also don't think there will be that many people that would create a sample setup....
So, please help us help you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply