Oracle CASE语句? [英] Oracle CASE statement?

问题描述

我有一个存储的proc.我在传递布尔值的地方，例如IS_ELIGIBLE. 现在，我希望能够编写如下查询:

I have a stored proc. where i am passing a boolean value e.g. IS_ELIGIBLE. Now i want to be able to write query something like this:

SELECT col1,
       CASE 
          WHEN IS_ELIGIBLE THEN col2 * 100
       ELSE
          col2 * 50
       END
         INTO OUT_COL1, OUT_SCORE
FROM TABLE_NAME

问题在于，由于IS_ELIGIBLE不是TABLE_NAME中的列之一，因此查询错误.我可以使用if..else编写相同的查询.

The problem is since IS_ELIGIBLE is not one of the column in TABLE_NAME, the query errors out. I can write the same query using if..else ie.

IF IS_ELIGIBLE
   SELECT col1, col2 * 100
ELSE
   SELECT col1, col2 * 50
END

但是我将重复执行select语句两次.我知道我可以创建具有该select语句的函数，这样我就不必重复两次.但是我只是好奇是否可以不做if..else或创建新功能而完成? 谢谢.

But i will be repeating select statement twice. I know i can create the function to have that select statement so that i don't have to repeat twice. But i was just curious if it can be done without doing if..else or creating new function? Thanks.

推荐答案

问题不是不是IS_ELIGIBLE不是表中的列，而是它是布尔值并且SQL无法处理布尔值(我知道，不要这样做).任务).因此，您需要引入另一个类似这样的变量:

The problem is not that IS_ELIGIBLE isn't a column in the table, but that it is a Boolean and SQL cannot handle Boolean values (I know, don't ask). So you need to introduce another variable something like this:

IS_ELIGIBLE_NUM NUMBER := CASE WHEN IS_ELIGIBLE THEN 1 ELSE 0 END;
..
SELECT col1,
   CASE 
      WHEN IS_ELIGIBLE_NUM = 1 THEN col2 * 100
   ELSE
      col2 * 50
   END
     INTO OUT_COL1, OUT_SCORE
FROM TABLE_NAME

这篇关于Oracle CASE语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！