我有一个这样的sql查询,它给出了一个语法错误,但我找不到它是什么:
CREATE VIEW
vw_hs_hr_wf_main_data AS
select mo.wfmod_id,
mo.wfmod_name,
mo.wfmod_view_name,
ty.wftype_code,
ty.wftype_description,
ty.wftype_table_name,
ty.wftype_view_name,
ma.wfmain_id,
ma.wfmain_sequence,
CASE
WHEN ap.wf_main_app_employee IS NULL
THEN ma.wfmain_approving_emp_number
ELSE ap.wf_main_app_employee
END CASE AS wfmain_approving_emp_number,
ma.wfmain_flow_id,
ty.wftype_update_field,
ty.wftype_assembly,
ty.wftype_assembly_class,
ma.wfmain_previous_id,
ty.wftype_cancelmain_field,
ma.wfmain_application_date,
ty.wftype_cancelstatus_field,
ty.wftype_appmain_field,
ty.wftype_bulk_app_flg,
ty.wftype_sort_field_name
FROM hs_hr_wf_module mo INNER JOIN hs_hr_wf_type ty
ON mo.wfmod_id = ty.wfmod_id
INNER JOIN hs_hr_wf_main ma
ON ty.wftype_code = ma.wftype_code
LEFT JOIN hs_hr_wf_main_app_person ap
ON ma.wfmain_id = ap.wfmain_id
AND ma.wfmain_sequence = ap.wfmain_sequence
WHERE (ma.wfmain_iscomplete_flg = 0);
错误消息:
#1064-您的SQL语法出现错误;在第11行的"CASE AS wfmain_approving_emp_number,ma.wfmain_flow_id,ty.wftype_update_filel"附近,查看与MySQL服务器版本相对应的手册,以获得要使用的正确语法
关闭CASE
不需要END CASE
,只需要END
:
END AS wfmain_approving_emp_number,
在您的情况下,一个更好的选择是COALESCE
呼叫:
COALESCE(ap.wf_main_app_employee, ma.wfmain_approving_emp_number)
AS wfmain_approving_emp_number