Teradata 13: CASE expression in Join

Question

Is CASE expression allowed in join? Is there a good way to accomplish the task here? My real query has some other left join.

I would like to join T1 and T2 in this condition:

when T1.sub_service is not null, then join with T2.type
when T1.sub_service is null, then use T1.service to join with T2.type

SELECT T1.service, T1.sub_service, T2.type FROM TABLE1 T1 LEFT JOIN TABLE2 T2 ON T2.type LIKE CASE WHEN T1.sub_service IS NULL THEN T1.service WHEN T1.sub_service IS NOT NULL THEN T1.sub_service END

dnoeth · Accepted Answer · 2015-03-04 17:26:27Z

4

Simply replace the LIKE with =:

ON T2.type = 
   CASE WHEN T1.sub_service IS NULL THEN T1.service
        WHEN T1.sub_service IS NOT NULL THEN T1.sub_service
   END

But you can further simplify this to a COALESCE:

ON T2.type = COALESCE(T1.sub_service, T1.service)

answered Mar 4, 2015 at 17:26

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Can I use "like" instead of "=" because there are some type I need to use "like" in order to get match some types.

I don't understand. Can you show some examples what you actually try to do?

I changed the query to "ON COALESCE(T1.sub_service, T1.service) LIKE T2.type" and it works. I get the right result. Thanks a lot for help!

If this is your actual condition it's the same as using equality (unless there are some trailing blanks), but might result in a bad plan.

Teradata doesn't ignore trailing blanks in a join?

|