时间戳

首页 > 解决方案 > oracle 查询 if else

oracle - oracle 查询 if else

问题描述

下面的查询如何在 oracle 数据库中处理，我在 Toad 中尝试

Query1 - SELECT T_BASIS_ACCESS_ID FROM ECKERNEL_MCA.T_BASIS_ACCESS WHERE ROLE_ID LIKE 'MCA.GFS.LEAD'
Query2 - SELECT OBJECT_ID, NAME  FROM ECKERNEL_MCA.OV_AREA WHERE END_DATE IS NULL AND OBJECT_ID  IN
         (SELECT DISTINCT REPLACE(REPLACE(REPLACE(ATTRIBUTE_TEXT, '(', '' ),')',''), '''', '') 
         FROM ECKERNEL_MCA.T_BASIS_OBJECT_PARTITION WHERE T_BASIS_ACCESS_ID IN 
         (SELECT T_BASIS_ACCESS_ID FROM ECKERNEL_MCA.T_BASIS_ACCESS WHERE ROLE_ID LIKE 'MCA.GFS.LEAD') )
Query3 - SELECT OBJECT_ID, NAME  FROM ECKERNEL_MCA.OV_AREA WHERE END_DATE IS NULL

标签： oracletoad

解决方案

试试这个。它应该按照您在评论中指出的方式行事。

我想如果 Query1 从表中返回任何结果，比如记录数为 1+，然后执行 Query2，如果记录为零，则执行 Query 3 if (Query1 > 0) 执行 Query2 否则执行 Query3

--Query 1
with Q_1 as
(SELECT T_BASIS_ACCESS_ID FROM ECKERNEL_MCA.T_BASIS_ACCESS WHERE ROLE_ID LIKE 'MCA.GFS.LEAD')

--Query 2
SELECT OBJECT_ID, NAME  FROM ECKERNEL_MCA.OV_AREA WHERE END_DATE IS NULL AND OBJECT_ID  IN
     (SELECT DISTINCT REPLACE(REPLACE(REPLACE(ATTRIBUTE_TEXT, '(', '' ),')',''), '''', '') 
     FROM ECKERNEL_MCA.T_BASIS_OBJECT_PARTITION WHERE T_BASIS_ACCESS_ID IN 
     (SELECT T_BASIS_ACCESS_ID FROM ECKERNEL_MCA.T_BASIS_ACCESS WHERE ROLE_ID LIKE 'MCA.GFS.LEAD') )
--If Query 1 returns anything
WHERE EXISTS (SELECT 1 FROM Q_1)

UNION ALL

--Query 3
SELECT OBJECT_ID, NAME  FROM ECKERNEL_MCA.OV_AREA WHERE END_DATE IS NULL
--If Query 1 returns nothing
WHERE NOT EXISTS (SELECT 1 FROM Q_1)

推荐阅读