What am I doing wrong in this SQL Query?

Question

I'm new to if-then SQL statements. I only know the basics (select, update, insert with joins etc.), so it will be helpful if you could help me with the syntax in this scenario.

I have a table that holds customer's activities, let's say I'm a dentist, and I store a specific activity, and when using my software to check out what the customer should pay, I use this query:

SELECT ACTIVITY.ID, 
       ACTIVITY.DATES, 
       ACTIVITY.INSURANCE_ID 
       ACTIVITY.AMOUNT, 
       ACTIVITY.INSURANCE_AMOUNT, 
       ACTIVITY.AMOUNT * ((100 - ACTIVITY.INSURANCE_AMOUNT) / 100) AS AMOUNT_TO_PAY, 
       ACTIVITY.TIME, 
       ACTIVITY.MONEY_RECEIVED,
       ACTIVITY.ROWID
FROM ACTIVITY
LEFT JOIN INSURANCE ON INSURANCE.ID = ACTIVITY.INSURANCE_ID 
WHERE ACTIVITY.ID = :patient_id AND ACTIVITY.MONEY_RECEIVED IS NULL

This query selects the data I need plus the amount that the customer should pay, calculating the percentage discount from the insurance amount and returning the total amount to pay as money (AMOUNT_TO_PAY).

This works fine, but the problem is, this calculation happens even if the insurance is expired. I want to make a SQL statement with if-then or whatever other method in ORACLE JDEVELOPER, that first will check if my table CUSTOMER_INSURANCE.TO_DATE is still active; if it is, do the calculation; if the DATE is expired, do not include the insurance percentage.

Here is the query I'm trying to execute but I'm doing something wrong:

IF ((SELECT count(*) FROM CUSTOMER_INSURANCE.TO_DATE TO_DATE 
    WHERE TO_DATE >= sysdate AND customer_id = :patient_id) > 0)
SELECT ACTIVITY.ID, 
       ACTIVITY.DATES, 
       ACTIVITY.INSURANCE_ID 
       ACTIVITY.AMOUNT, 
       ACTIVITY.INSURANCE_AMOUNT, 
       ACTIVITY.AMOUNT * ((100 - ACTIVITY.INSURANCE_AMOUNT) / 100) AS AMOUNT_TO_PAY, 
       ACTIVITY.TIME, 
       ACTIVITY.MONEY_RECEIVED,
       ACTIVITY.ROWID
FROM ACTIVITY
LEFT JOIN INSURANCE ON INSURANCE.ID = ACTIVITY.INSURANCE_ID 
WHERE ACTIVITY.ID = :patient_id AND ACTIVITY.MONEY_RECEIVED IS NULL
ELSE IF ((SELECT count(*) FROM CUSTOMER_INSURANCE.TO_DATE TO_DATE 
    WHERE TO_DATE >= sysdate AND customer_id = :patient_id) = 0)
SELECT ACTIVITY.ID, 
       ACTIVITY.DATES, 
       ACTIVITY.INSURANCE_ID 
       ACTIVITY.AMOUNT, 
       ACTIVITY.INSURANCE_AMOUNT, 
       ACTIVITY.AMOUNT AS AMOUNT_TO_PAY, 
       ACTIVITY.TIME, 
       ACTIVITY.MONEY_RECEIVED,
       ACTIVITY.ROWID
FROM ACTIVITY
LEFT JOIN INSURANCE ON INSURANCE.ID = ACTIVITY.INSURANCE_ID 
WHERE ACTIVITY.ID = :patient_id AND ACTIVITY.MONEY_RECEIVED IS NULL

Can anyone help me finish this query?

Answer 1

If you are using Oracle then it is a bad practice to use the keyword TO_DATE or DATES, TIME as column alias which might throw an error. Also you do not need IF ELSE statement since this can be achieved in the normal UNION ALL.

SELECT
      ACTIVITY.ID,
      ACTIVITY.DATES,
      ACTIVITY.INSURANCE_ID,
      ACTIVITY.AMOUNT,
      ACTIVITY.INSURANCE_AMOUNT,
      CASE
          WHEN TO_DATE >= SYSDATE
          THEN
              ACTIVITY.AMOUNT
              * ( ( 100
                  - ACTIVITY.INSURANCE_AMOUNT )
                / 100 )
          ELSE
              ACTIVITY.AMOUNT
      END
          AS AMOUNT_TO_PAY,
      ACTIVITY.TIME,
      ACTIVITY.MONEY_RECEIVED,
      ACTIVITY.ROWID
FROM
      CUSTOMER_INSURANCE,
          ACTIVITY
      LEFT JOIN
          INSURANCE
      ON INSURANCE.ID = ACTIVITY.INSURANCE_ID
WHERE
         ACTIVITY.ID = :PATIENT_ID
      AND ACTIVITY.MONEY_RECEIVED IS NULL
      AND CUSTOMER_ID = :PATIENT_ID

Answer 2

You can add a CASE expression to return the ACTIVITY.INSURANCE_AMOUNT value conditionally:

SELECT ACTIVITY.ID, 
       ACTIVITY.DATES, 
       ACTIVITY.INSURANCE_ID 
       ACTIVITY.AMOUNT, 
       ACTIVITY.INSURANCE_AMOUNT, 
       ACTIVITY.AMOUNT * (1 - CASE
                                WHEN EXISTS (
                                  SELECT *
                                  FROM CUSTOMER_INSURANCE.TO_DATE
                                  WHERE TO_DATE >= sysdate
                                    AND customer_id = :patient_id
                                )
                                THEN ACTIVITY.INSURANCE_AMOUNT
                                ELSE 0
                              END / 100) AS AMOUNT_TO_PAY,
       ACTIVITY.TIME, 
       ACTIVITY.MONEY_RECEIVED,
       ACTIVITY.ROWID

FROM ACTIVITY

LEFT JOIN INSURANCE ON INSURANCE.ID = ACTIVITY.INSURANCE_ID 

WHERE ACTIVITY.ID = :patient_id AND ACTIVITY.MONEY_RECEIVED IS NULL
;

When there are matching rows in CUSTOMER_INSURANCE.TO_DATE, the ACTIVITY.INSURANCE_AMOUNT value is returned to calculate the remaining amount, otherwise 0 is returned and so the entire expression evaluates to just ACTIVITY.AMOUNT.

Notes:

1. The "percentage to pay" calculation was changed from (100 - x) / 100 to the equivalent (and slightly shorter) 1 - x/100 form.

2. The (SELECT COUNT(*) FROM ...) > 0 predicate was replaced with the possibly more efficient EXISTS (SELECT * FROM ...) one.

Answer 3

I think this should work. You can change the CASE based on your requirement.

select  ACTIVITY.ID, 
       ACTIVITY.DATES, 
       ACTIVITY.INSURANCE_ID 
       ACTIVITY.AMOUNT, 
       ACTIVITY.INSURANCE_AMOUNT,
       CASE WHEN  TO_DATE >= sysdate 
                THEN  ACTIVITY.AMOUNT * ((100 - ACTIVITY.INSURANCE_AMOUNT) / 100)
                WHEN TO_DATE < sysdate
                THEN  ACTIVITY.AMOUNT
                END AS AMOUNT_TO_PAY
FROM 
(
SELECT ACTIVITY.ID, 
       ACTIVITY.DATES, 
       ACTIVITY.INSURANCE_ID 
       ACTIVITY.AMOUNT, 
       ACTIVITY.INSURANCE_AMOUNT, 
       ACTIVITY.AMOUNT  
       ACTIVITY.TIME, 
       ACTIVITY.MONEY_RECEIVED,
       ACTIVITY.ROWID
FROM ACTIVITY
LEFT JOIN INSURANCE ON INSURANCE.ID = ACTIVITY.INSURANCE_ID 
WHERE ACTIVITY.ID = :patient_id AND ACTIVITY.MONEY_RECEIVED IS NULL
)
WHERE ACTIVITY.ID = :patient_id;

Thanks, Aditya

Answer 4

 IF CUSTOMER_INSURANCE.TO_DATE >= GetDate()  THEN
  ACTIVITY.AMOUNT * ((100 - ACTIVITY.INSURANCE_AMOUNT) / 100);
   ELSE ACTIVITY.AMOUNT;

Would the previous statment work? Be warned this has not been tested.