在请求 SQL(oracle) 的情况下注册一个条件，该条件在" not data found"时执行

Question

我有一个请求，它完全有效，没有问题。以下是查询：。

WITH rowsel as (select   OBL_TAREDAT   ,OBL_BRUTTODAT,DIRECTIONID,TARE,BRUTTO   from
(select  OBL_TAREDAT    ,OBL_BRUTTODAT,DIRECTIONID,TARE,BRUTTO from  WAYBILLS where EXTERNAL_ID = json_value ( {{CLOB}}, '$.data.external_id') 
and OBL_TTN is null))
SELECT CASE
WHEN  json_value ( {{CLOB}}, '$.data_weight.stable') = 'false'  THEN 81
WHEN rowsel.OBL_BRUTTODAT  is null and rowsel.DIRECTIONID = '1'   THEN 41
WHEN rowsel.OBL_TAREDAT  is null and rowsel.DIRECTIONID = '1'     THEN 21
WHEN rowsel.OBL_TAREDAT  is null and rowsel.DIRECTIONID = '-1'     THEN 62
WHEN rowsel.OBL_BRUTTODAT  is null and rowsel.DIRECTIONID = '-1'    THEN 61
ELSE 0 END
FROM rowsel

在它中，我在外部id上查找1条记录，并根据我通过事例找到的数据，选择我想在退出时接收的脚本。我希望当我在外部id上找不到任何记录时，我可以输入"；如果计数器＝0，则为1"；。我试过了，但没有成功，我会感谢你的帮助。

WITH rowsel as (select  id, OBL_TAREDAT    ,OBL_BRUTTODAT,DIRECTIONID,TARE,BRUTTO   from
(select id, OBL_TAREDAT ,OBL_BRUTTODAT,DIRECTIONID,TARE,BRUTTO from  WAYBILLS where EXTERNAL_ID = json_value ( {{CLOB}}, '$.data.external_id') 
and OBL_TTN is null))
SELECT CASE
WHEN count(rowsel.id) = 0 THEN 1
WHEN  json_value ( {{CLOB}}, '$.data_weight.stable') = 'false'  THEN 81
WHEN rowsel.OBL_BRUTTODAT  is null and rowsel.DIRECTIONID = '1'   THEN 41
WHEN rowsel.OBL_TAREDAT  is null and rowsel.DIRECTIONID = '1'     THEN 21
WHEN rowsel.OBL_TAREDAT  is null and rowsel.DIRECTIONID = '-1'     THEN 62
WHEN rowsel.OBL_BRUTTODAT  is null and rowsel.DIRECTIONID = '-1'    THEN 61
ELSE 0 END
FROM rowsel
group by rowsel.id, rowsel.OBL_TAREDAT  ,rowsel.OBL_BRUTTODAT,rowsel.DIRECTIONID,rowsel.TARE,rowsel.BRUTTO

Answer 1

我认为您需要UNION ALL和NOT EXISTS，如下所示：

WITH rowsel as (select   OBL_TAREDAT   ,OBL_BRUTTODAT,DIRECTIONID,TARE,BRUTTO   from
(select  OBL_TAREDAT    ,OBL_BRUTTODAT,DIRECTIONID,TARE,BRUTTO from  WAYBILLS where EXTERNAL_ID = json_value ( {{CLOB}}, '$.data.external_id') 
and OBL_TTN is null))
SELECT CASE
WHEN  json_value ( {{CLOB}}, '$.data_weight.stable') = 'false'  THEN 81
WHEN rowsel.OBL_BRUTTODAT  is null and rowsel.DIRECTIONID = '1'   THEN 41
WHEN rowsel.OBL_TAREDAT  is null and rowsel.DIRECTIONID = '1'     THEN 21
WHEN rowsel.OBL_TAREDAT  is null and rowsel.DIRECTIONID = '-1'     THEN 62
WHEN rowsel.OBL_BRUTTODAT  is null and rowsel.DIRECTIONID = '-1'    THEN 61
ELSE 0 END
FROM rowsel
UNION ALL
SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM rowsel)