Home > Enterprise >  Oracle SQL using CASE Statement
Oracle SQL using CASE Statement

Time:03-10

SELECT (
        SELECT DATE'2021-08-31' - MIN(DATE)
        FROM TABLE
        WHERE BAL LIKE '237%'
        ) AS DATE_DIF,
       ( SELECT SUMM
         FROM TABLE
         WHERE DAT = DATE'2021-08-31' AND BAL LIKE '237%'
         ) AS SALDO_PASSIVE
FROM DUAL;

Output is like

DATE_DIFF SALDO_PASSIVE
837 3263200

I have tried to use CASE before SUMM but it did not work. Here Date difference = 837 it is between 2-3 years, So output should be SUMM * 0,4. Or is there another way to do that?

CodePudding user response:

You have only mentioned the logic if year is between 2 and 3. If there are other conditions then you need to mention in case statement.

select
DATE_DIF,
(case DATE_DIF/365 between 2 and 3 then SALDO_PASSIVE*.04 else SALDO_PASSIVE end ) SALDO_PASSIVE

(
  SELECT (
        SELECT DATE'2021-08-31' - MIN(DATE)
        FROM TABLE
        WHERE BAL LIKE '237%'
        ) AS DATE_DIF,
       ( SELECT SUMM
         FROM TABLE
         WHERE DAT = DATE'2021-08-31' AND BAL LIKE '237%'
         ) AS SALDO_PASSIVE
  FROM DUAL
);
  • Related