我试图使用基于某些case语句而有所不同的join语句。当字段lane.dest_postal_code_prefix不为NULL时,我希望联接像下面的第一段代码一样。当field lane.dest_postal_code_prefix为NULL时,我希望联接像下面的第二个代码块一样。(请注意两种情况下ON条件的差异)
我需要一些帮助,将case语句添加到join子句中。
--WHEN lane.dest_postal_code_prefix is NOT NULL
SELECT * FROM big_bucket_bridge A
LEFT JOIN lane
ON
(
A.customer_country = lane.dest_country_code
AND
SUBSTRING( A.ddm_zip, 1, LENGTH( lane.dest_postal_code_prefix ) ) =
lane.dest_postal_code_prefix
)
WHERE
snapshot_day between '2019-06-23'-22 and '2019-06-23'
AND (is_before_cutoff_g OR (is_before_cutoff_opt_g and not under_two))
AND row_n =1
;
--WHEN lane.dest_postal_code_prefix is NULL
SELECT * FROM big_bucket_bridge A
LEFT JOIN lane
ON
(
A.customer_country = lane.dest_country_code
)
WHERE
snapshot_day between '2019-06-23'-22 and '2019-06-23'
AND (is_before_cutoff_g OR (is_before_cutoff_opt_g and not under_two))
AND row_n =1
;
您可以这样使用一个ON子句:
ON
(
A.customer_country = lane.dest_country_code
AND
COALESCE(SUBSTRING(A.ddm_zip, 1, LENGTH( lane.dest_postal_code_prefix )), '') = CASE
WHEN lane.dest_postal_code_prefix IS NOT NULL THEN lane.dest_postal_code_prefix
ELSE COALESCE(SUBSTRING(A.ddm_zip, 1, LENGTH( lane.dest_postal_code_prefix )), '')
END
)
在第二种情况下(其他部分),该条件始终为真,因为它是:
COALESCE(SUBSTRING(A.ddm_zip, 1, LENGTH( lane.dest_postal_code_prefix )), '') = COALESCE(SUBSTRING(A.ddm_zip, 1, LENGTH( lane.dest_postal_code_prefix )), '')
所以唯一的实际情况是
A.customer_country = lane.dest_country_code
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句