Case in the Join ...On..

Question

Case in the Join ...On..

Yelena Varshal

SSC-Dedicated

Points: 34286
More actions
June 16, 2005 at 9:46 am

#67712

Hello,
Did someone use Case in the ON part of the outer join as a low-volume lookup? I have so far just a couple of values in 2 tables that are not the same but consistent. Like Value1 in one table always stays for 111 in another table. It probably does not make sence from the point of view of application architecture and maintenance to create an individual lookup table for just 2 or 3 values while all other records are consistent in both table and those (all other) records change over time
Performance is not an issue at all.
What do you think?
Example:
.....FROM table1 LEFT OUTER JOIN table2
ON Table1.field1 = CASE
                When Table2.Field2 = 111 then 'Value1'
                 Else onvert(varchar(20),table2.filed2)
                    End

Regards,Yelena Varsha

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

If it works, go for it.

If you need performance later on. Maybe you can create a computed column (index) that does the case work, and you'd still have pretty good perf out of this.

Hans Lindgren SSChampion Points: 10496 More actions · Answer 2

I agree with Remi Gregoire, your solution is great. Easy to make, quick to implement and if performance is not an issue I would keep it as it is right now. I have used the same approach some numer of times to great satisfaction

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Remi and Hans,

Thanks for your feedback. I will use this query then. It will be a line in several quarterly reports, that is why I said performance is not an issue. It runs in seconds anyway. Those reports will be run only once each, once in 3 month with the output into Excel.

Yelena

Regards,Yelena Varsha