在 sql 查询 (oracle DB) 中使用 CASE 语句出现意外结果

发布于 2024-12-28 04:21:00 字数 1619 浏览 1 评论 0原文

我有这个查询

SELECT  
   CASE WHEN EXISTS (SELECT PARM_VALUE 
                       FROM BO_PARM  
                      WHERE (ENTE_CD = '7316') 
                        AND PARM_CD = 'PAGINAZIONE_PROMOZIONI')  
        THEN p2.PARM_VALUE 
        ELSE p1.PARM_VALUE 
    END as RIGHE 
  FROM
       (SELECT PARM_VALUE 
          FROM BO_PARM  
         WHERE (ENTE_CD = '0000' OR ENTE_CD = 'XXXX') 
           AND PARM_CD = 'PAGINAZIONE_PROMOZIONI') p1,
       (SELECT PARM_VALUE 
          FROM BO_PARM  
         WHERE (ENTE_CD = '7316') 
           AND PARM_CD = 'PAGINAZIONE_PROMOZIONI') p2

基本上，当第二个查询至少有一行时，我想返回第二个查询的值，否则返回第一个查询的结果。现在这些是返回值：

SELECT PARM_VALUE 
  FROM BO_PARM  
  WHERE (ENTE_CD = '7316') 
    AND PARM_CD = 'PAGINAZIONE_PROMOZIONI' //No retrun

SELECT PARM_VALUE 
  FROM BO_PARM  
 WHERE (ENTE_CD = '0000' OR ENTE_CD = 'XXXX') 
  AND PARM_CD = 'PAGINAZIONE_PROMOZIONI' //returns 10

SELECT  
    CASE WHEN EXISTS (SELECT PARM_VALUE 
                        FROM BO_PARM  
                       WHERE (ENTE_CD = '7316') 
                         AND PARM_CD = 'PAGINAZIONE_PROMOZIONI')  
         THEN p2.PARM_VALUE 
         ELSE p1.PARM_VALUE 
     END as RIGHE 
  FROM
       (SELECT PARM_VALUE 
          FROM BO_PARM  
         WHERE (ENTE_CD = '0000' OR ENTE_CD = 'XXXX') 
           AND PARM_CD = 'PAGINAZIONE_PROMOZIONI') p1,
       (SELECT PARM_VALUE 
          FROM BO_PARM  
         WHERE (ENTE_CD = '7316') 
           AND PARM_CD = 'PAGINAZIONE_PROMOZIONI') p2  //no return, i was expecting 10

我在 CASE 语句中做错了什么？

原文

i have this query

SELECT  
   CASE WHEN EXISTS (SELECT PARM_VALUE 
                       FROM BO_PARM  
                      WHERE (ENTE_CD = '7316') 
                        AND PARM_CD = 'PAGINAZIONE_PROMOZIONI')  
        THEN p2.PARM_VALUE 
        ELSE p1.PARM_VALUE 
    END as RIGHE 
  FROM
       (SELECT PARM_VALUE 
          FROM BO_PARM  
         WHERE (ENTE_CD = '0000' OR ENTE_CD = 'XXXX') 
           AND PARM_CD = 'PAGINAZIONE_PROMOZIONI') p1,
       (SELECT PARM_VALUE 
          FROM BO_PARM  
         WHERE (ENTE_CD = '7316') 
           AND PARM_CD = 'PAGINAZIONE_PROMOZIONI') p2

Basically i want to return the Value of the second query when the second query hase at least one row otherwise return the result of the first query. Right now these are the return values:

SELECT PARM_VALUE 
  FROM BO_PARM  
  WHERE (ENTE_CD = '7316') 
    AND PARM_CD = 'PAGINAZIONE_PROMOZIONI' //No retrun

SELECT PARM_VALUE 
  FROM BO_PARM  
 WHERE (ENTE_CD = '0000' OR ENTE_CD = 'XXXX') 
  AND PARM_CD = 'PAGINAZIONE_PROMOZIONI' //returns 10

SELECT  
    CASE WHEN EXISTS (SELECT PARM_VALUE 
                        FROM BO_PARM  
                       WHERE (ENTE_CD = '7316') 
                         AND PARM_CD = 'PAGINAZIONE_PROMOZIONI')  
         THEN p2.PARM_VALUE 
         ELSE p1.PARM_VALUE 
     END as RIGHE 
  FROM
       (SELECT PARM_VALUE 
          FROM BO_PARM  
         WHERE (ENTE_CD = '0000' OR ENTE_CD = 'XXXX') 
           AND PARM_CD = 'PAGINAZIONE_PROMOZIONI') p1,
       (SELECT PARM_VALUE 
          FROM BO_PARM  
         WHERE (ENTE_CD = '7316') 
           AND PARM_CD = 'PAGINAZIONE_PROMOZIONI') p2  //no return, i was expecting 10

what am i doing wrong in the CASE statement?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小苏打饼 2025-01-04 04:21:00

您现有的查询在两个子查询之间有隐含的笛卡尔连接 - 这意味着一个查询返回 n 行，另一个查询返回 m 行，您将看到总计返回 m * n 行 - 即。第一组中的行与第二组中的行的每个组合。

因此，如果任一集中返回 0 行，您将看到总共返回 0 行。

假设两个查询都不应该返回多于一行，那么修改后的查询的最简单版本可能如下所示：

SELECT coalesce(
       (SELECT PARM_VALUE FROM BO_PARM  WHERE (ENTE_CD = '7316') AND PARM_CD = 'PAGINAZIONE_PROMOZIONI'),
       (SELECT PARM_VALUE FROM BO_PARM  WHERE (ENTE_CD = '0000' OR ENTE_CD = 'XXXX') AND PARM_CD = 'PAGINAZIONE_PROMOZIONI')
                )

Your existing query has an implied cartesian join between the two sub-queries - this means that where one query returns n rows and the other returns m rows, you will see a total of m * n rows returned - ie. every combination of rows from the first set with rows from the second set.

So if you have 0 rows being returned in either set, you will see 0 rows being returned in total.

Assuming neither query should return more than one row, then probably the simplest version of the amended query might be as follows:

SELECT coalesce(
       (SELECT PARM_VALUE FROM BO_PARM  WHERE (ENTE_CD = '7316') AND PARM_CD = 'PAGINAZIONE_PROMOZIONI'),
       (SELECT PARM_VALUE FROM BO_PARM  WHERE (ENTE_CD = '0000' OR ENTE_CD = 'XXXX') AND PARM_CD = 'PAGINAZIONE_PROMOZIONI')
                )

回复收藏 0 原文

徒留西风 2025-01-04 04:21:00

您正在对两个视图进行隐式联接，其中一个视图为空（p1）。

这可以是一种方法：

with a as
(SELECT 
  PARM_VALUE, 
  case when (ENTE_CD = '0000' OR ENTE_CD = 'XXXX') then 1
       when ENTE_CD = '7316' then 2
  end as rnk
FROM BO_PARM  
WHERE PARM_CD = 'PAGINAZIONE_PROMOZIONI' AND  
((ENTE_CD = '0000' OR ENTE_CD = 'XXXX') OR  (ENTE_CD = '7316') )
)
select PARM_VALUE 
from a
where rnk = (select min(rnk) from a)

You are doing a implicit join on two views with one of them being empty(p1).

This can be an aproach:

with a as
(SELECT 
  PARM_VALUE, 
  case when (ENTE_CD = '0000' OR ENTE_CD = 'XXXX') then 1
       when ENTE_CD = '7316' then 2
  end as rnk
FROM BO_PARM  
WHERE PARM_CD = 'PAGINAZIONE_PROMOZIONI' AND  
((ENTE_CD = '0000' OR ENTE_CD = 'XXXX') OR  (ENTE_CD = '7316') )
)
select PARM_VALUE 
from a
where rnk = (select min(rnk) from a)

回复收藏 0 原文

~没有更多了~