当前位置: 动力学知识库 > 问答 > 编程问答 >

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

网友答案:

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)
分享给朋友:
您可能感兴趣的文章:
随机阅读: