teradata

Teradata 13: CASE expression in Join

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:

  1. when T1.sub_service is not null, then join with T2.type

  2. 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

Solution

  • 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)