CODEWITHSUNDEEP
teradata
angelcake
angelcake

Reputation: 119

Teradata 13: CASE statement in Join

Is CASE statement 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

Upvotes: 1

Views: 8872

Answers (1)

dnoeth
dnoeth

Reputation: 60462

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)

Upvotes: 4

Related Questions