补充一个方便复现的case:
SELECT
/*+ SET_VAR(“spill_mode”= ‘auto’, “new_planner_optimize_timeout”= ‘3000000’) */
entry AS entry, subject_name_json
FROM (SELECT t7.entry, CASE WHEN CAST(CAST(t7.entry1 AS JSON) -> ‘FACCOUNTID’ -> ‘FullName’ AS VARCHAR) = ‘[]’ THEN ‘’ ELSE t8.value END AS subject_name_json
FROM ((SELECT entry, entry AS entry1
FROM (SELECT t3.entry, CASE WHEN CAST(CAST(t3.entry1 AS JSON) -> ‘FCURRENCYID’ -> ‘Name’ AS VARCHAR) = ‘[]’ THEN ‘’ ELSE t4.value END AS currency_json
FROM ((SELECT entry, entry AS entry1
FROM (SELECT CASE WHEN t.value IS NULL THEN NULL ELSE t.value END AS entry
FROM ((SELECT _tmp_ods_api_kidxk_gl_voucher_info_f2.GL_VOUCHERENTRY
FROM hpp._tmp_ods_api_kidxk_gl_voucher_info_f2) AS _tmp_ods_api_kidxk_gl_voucher_info_f2 INNER JOIN JSON_EACH(CASE WHEN CAST(CASE WHEN GL_VOUCHERENTRY = ‘[]’ THEN NULL ELSE GL_VOUCHERENTRY END AS JSON) IS NULL THEN ‘{“mock”:null}’ ELSE CAST(CASE WHEN GL_VOUCHERENTRY = ‘[]’ THEN NULL ELSE GL_VOUCHERENTRY END AS JSON) END) AS t ON TRUE)) AS t0
WHERE CAST(CAST(entry AS JSON) -> ‘Id’ AS VARCHAR) <> ‘0’) AS t3 INNER JOIN JSON_EACH(CASE WHEN CAST(CAST(entry1 AS JSON) -> ‘FCURRENCYID’ -> ‘Name’ AS JSON) IS NULL THEN ‘{“mock”:null}’ ELSE CAST(CAST(entry1 AS JSON) -> ‘FCURRENCYID’ -> ‘Name’ AS JSON) END) AS t4 ON TRUE)) AS t5) AS t7 INNER JOIN JSON_EACH(CASE WHEN CAST(CAST(entry1 AS JSON) -> ‘FACCOUNTID’ -> ‘FullName’ AS JSON) IS NULL THEN ‘{“mock”:null}’ ELSE CAST(CAST(entry1 AS JSON) -> ‘FACCOUNTID’ -> ‘FullName’ AS JSON) END) AS t8 ON TRUE)) AS t9
