如何使用 CASE/WHEN 对 SQL 中的范围值进行分类？如果不可能，还有其他选择吗？

Question

基本上我希望能够将所选的列分类到不同的范围，如下所示： 如果范围>大于 0 返回列的值。 如果范围<大于 0 返回“已过期”。 这是代码：

 SELECT 
    PACKAGE_ID,
    PACKAGE_NAME,
    ORG_ID, 
    ORG_NAME, 
    VALID_FROM,
    VALID_TO,
    DAYS_LEFT,
    TOTAL, 
    BALANCE,
    THRESHOLD,
    REPORTID 

 FROM
( SELECT  
    B.ORG_ID, 
    B.ORG_NAME, 
    A.PACKAGE_ID,
    A.NAME AS PACKAGE_NAME,
    A.BALANCE AS BALANCE,
    A.VALID_FROM,
    A.VALID_TO,
    C.REPORT_CODE,
     Floor(A.VALID_TO - (SELECT current_date FROM dual))  AS DAYS_LEFT, 
    X.TOTAL AS TOTAL, 
    CASE X.TOTAL  WHEN 0 THEN 0 ELSE A.BALANCE / X.TOTAL* 100  END AS THRESHOLD,    
    'MIS0014' AS REPORTID

    FROM 
    PREPAID_PACKAGE A 
        JOIN MST_ORGANISATION B ON B.ORG_ID = A.ORG_ID
            JOIN PREPAID_REPORT C  ON C.PACKAGE_ID = A.PACKAGE_ID
                    JOIN (
                       SELECT
                       SUM(Z.TOPUP_VALUE) as TOTAL,                       
                        Y.ORG_ID AS ORG_ID, 
                        Y.PACKAGE_ID AS PACKAGE_ID
                        from PREPAID_VALUE Z 
                            JOIN PREPAID_PACKAGE Y on  Y.PACKAGE_ID =Z.PACKAGE_ID
                            GROUP BY Y.ORG_ID, Y.PACKAGE_ID
                            )  X ON X.ORG_ID=A.ORG_ID AND X.PACKAGE_ID=A.PACKAGE_ID

   ORDER BY 
    ORG_CODE, 
    PACKAGE_ID,
    PACKAGE_NAME,
    C.REPORT_CODE)  S 

   WHERE
    REPORTID='MIS0014'
    AND ORG_ID = 1
    AND VALID_FROM >= TO_DATE('01/02/2007', 'dd/mm/yyyy') 
    AND VALID_TO < TO_DATE('01/02/2013', 'dd/mm/yyyy')
    AND THRESHOLD < 20
    AND DAYS_LEFT < 13

   GROUP BY
    PACKAGE_ID,
    PACKAGE_NAME,
    ORG_ID, 
    ORG_NAME, 
    DAYS_LEFT,
    VALID_FROM,
    VALID_TO,
    REPORTID,
    TOTAL,
    BALANCE,
    THRESHOLD,
    REPORTID

我想将以下语句：更改

Floor(A.VALID_TO - (SELECT current_date FROM dual))  AS DAYS_LEFT, 

为以下：

when Floor(A.VALID_TO - (SELECT current_date FROM dual)) > 0 then Floor(A.VALID_TO - (SELECT current_date FROM dual)) else 'Expired' end AS DAYS_LEFT,

或类似的内容， 是否有任何关键字/函数可以让我这样做？

Answer 1

既然您已经有了内联查询，为什么不在主 SELECT 中执行它呢？

例如

SELECT 
    PACKAGE_ID,
    PACKAGE_NAME,
    ORG_ID, 
    ORG_NAME, 
    VALID_FROM,
    VALID_TO,
    TOTAL, 
    BALANCE,
    THRESHOLD,
    REPORTID,

   CASE 
       WHEN DAYS_LEFT > 0 THEN DAYS_LEFT -- Convert it to a Character of course
       ELSE 'Expired' 
   END as DAYS_LEFT

Answer 2

您似乎已经几乎正确了...但这是正确的语法：

CASE WHEN FLOOR(A.VALID_TO - SYSDATE) > 0 
THEN FLOOR(A.VALID_TO - SYSDATE) 
ELSE 'Expired' 
END AS DAYS_LEFT,

如果 Oracle 抱怨您不匹配数据类型，则可能意味着您需要转换 FLOOR(A.VALID_TO - SYSDATE) 通过将其包装在 CAST 函数中来转换为 varchar2：CAST(FLOOR(A.VALID_TO - SYSDATE) AS VARCHAR2(5)) - 但是这可能根本没有必要。