Databases
StackOverflow2013
Postsdbo
POWhat am I doing wrong in this SQL Query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat am I doing wrong in this SQL Query?
    primarykey
    data
    text
    <p>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.</p> <p>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:</p> <pre><code>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 </code></pre> <p>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).</p> <p>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.</p> <p>Here is the query I'm trying to execute but I'm doing something wrong:</p> <pre><code>IF ((SELECT count(*) FROM CUSTOMER_INSURANCE.TO_DATE TO_DATE WHERE TO_DATE &gt;= sysdate AND customer_id = :patient_id) &gt; 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 &gt;= 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 </code></pre> <p>Can anyone help me finish this query?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPang
    1. USPhil
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhat am I doing wrong in this SQL Query?
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POWhat am I doing wrong in this SQL Query?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThere is not `IF` in Oracle SQL. If can only be used in PL/SQL inside a stored procedure/function.
      singulars
      1. POWhat am I doing wrong in this SQL Query?
      1. USa_horse_with_no_name
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload