在配置单元中使用CASE作为联接条件

Question

我正试图在配置单元中运行一个查询，在该查询中，我基于case语句运行联接。出于某种原因，我在第7行和第8行遇到了问题。我无法解决错误

第7行：应为：AND、AS、BETWEEN、DIV、ILIKE、IN、IREGEXP、IS、LIKE、NOT、OR、REGEXP、RLIKE原因：异常：语法错误

第8行：遇到：如预期：AND、BETWEEN、DIV、ILIKE、IN、IREGEXP、IS、LIKE、NOT、OR、REGEXP、RLIKE原因：异常：语法错误

select * from dra_record_set.mark_set inv 
INNER JOIN innerdb.name_set roll_table
on inv.record_id = roll_table.ply_record_id AND
roll_table.date =
(CASE
WHEN inv.purchase_day>0 AND inv.purhcase_date BETWEEN roll_table.discount_start_dt AND roll_table.discount_end_dt) THEN inv.purchase_date
ELSE WHEN (CONCAT(inv.purchase_yr,inv.purchase_mo,(CAST("15")AS INT))) AS temp_var BETWEEN roll_table.discount_start_dt AND roll_table.discount_end_dt) THEN inv.purchase_date
END AS temp_pur_dt)
WHERE inv.inroll_discount_eligible_flag =1
limit 10

Answer 1

case是表达式。它返回一个值。您的似乎更像是SQL代码的宏替换。

删除case，只使用布尔逻辑：

from dra_record_set.mark_set inv join
innerdb.name_set roll_table
on inv.record_id = roll_table.ply_record_id and
( (inv.purchase_day > 0 and
inv.purhcase_date between roll_table.discount_start_dt and roll_table.discount_end_dt
) or
(inv.purchase_day <= 0 and
CONCAT(inv.purchase_yr, inv.purchase_mo, 15) between roll_table.discount_start_dt AND roll_table.discount_end_dt
)
)