Question

SELECT vendor_name, TO_CHAR(INVOICE_TOTAL - (PAYMENT_TOTAL + CREDIT_TOTAL),'$999,999.90') AS "AMTDUE"
2 FROM AP.VENDORS
3 JOIN AP.INVOICES
4 ON vendors.vendor_id = invoices.vendor_id
5 GROUP BY vendor_name
6 WHERE (INVOICE_TOTAL - (PAYMENT_TOTAL + CREDIT_TOTAL)) > 0;
WHERE (INVOICE_TOTAL - (PAYMENT_TOTAL + CREDIT_TOTAL)) > 0
*
ERROR at line 6:
ORA-00933: SQL command not properly ended

Answer 1

Dear Student ,

As per requirement submitted above kindly find below solution.

Error details :

• group by should be the last clause
• where need to be used before the group by clause
• This SQL query needs to use having clause

Below is the correct query

SELECT
   vendor_name,
   TO_CHAR(INVOICE_TOTAL - (PAYMENT_TOTAL + CREDIT_TOTAL),'$999,999.90') AS "AMTDUE"
FROM
   VENDORS JOIN INVOICES ON vendors.vendor_id = invoices.vendor_id
GROUP BY vendor_name
having (INVOICE_TOTAL - (PAYMENT_TOTAL + CREDIT_TOTAL)) > 0;

NOTE :PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.