1

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
angelcake
  • 119
  • 5
  • 7
  • 18

1 Answers1

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)
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Can I use "like" instead of "=" because there are some type I need to use "like" in order to get match some types. – angelcake Mar 04 '15 at 17:36
  • I don't understand. Can you show some examples what you actually try to do? – dnoeth Mar 04 '15 at 17:59
  • 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! – angelcake Mar 04 '15 at 18:26
  • 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. – dnoeth Mar 04 '15 at 18:55
  • Teradata doesn't ignore trailing blanks in a join? – Andrew Mar 04 '15 at 22:49
  • Trailing blanks are ignored for a comparison using =/>, but LIKE has different semantics. **'a' LIKE 'a '** returns false, but **'a' = 'a ' returns** true (and this is Standard SQL behaviour) – dnoeth Mar 05 '15 at 06:40
  • I would simplify the whole `ON` clause, to: `ON T1.sub_service IS NULL AND T2.type LIKE T1.service OR T2.Type LIKE T1.sub_service` – ypercubeᵀᴹ Mar 09 '15 at 20:08