How to use CASE/WHEN to categorise ranged values in SQL? If not possible, any alternatives?

Basically I want to be able to categorize the column selected into different range like so: if the range > than 0 return the value of the column. if the range < than 0 return 'Expired'. Here is the code:

 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

I'd like to change the following statement :

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

to the following:

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,

or something similar, Is there any keyword/function to allow me to do it?

Since you've already got an inline query why not just do it in your main SELECT.

e.g.

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

You seem to have it almost correct already... but here's the correct syntax:

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

In case Oracle complains you about not matching datatypes, it may mean that you need to cast the FLOOR(A.VALID_TO - SYSDATE) to varchar2 by wrapping it inside a CAST function: CAST(FLOOR(A.VALID_TO - SYSDATE) AS VARCHAR2(5)) - but that may not be necessary at all.