CREATE VIEW open_items_summary AS SELECT vendor_name, (invoice_total - payment_t
ID: 3660249 • Letter: C
Question
CREATE VIEW open_items_summary AS
SELECT vendor_name, (invoice_total - payment_total - credit_total) AS balance_due
FROM vendors
WHERE balance_due IN
(
SELECT COUNT(balance_due) AS open_item_count,
SUM(balance_due) AS open_item_total
FROM invoices
GROUP BY vendor_name
HAVING balance_due > 0
ORDER BY open_item_total DESC
);
When I run the query in SQL Developer, the error message is "missing right parenthesis". I've added and taken off the parenthesis and restarted program but nothing. This is the only question I do not have correct for an assignment due tonight by midnight so a quick response would be appreciated! Thanks
Explanation / Answer
Plz Rate it With LifeSaver..
Errors:
1) The ORDER BY clause is invalid in views
2) Inner query is retriving zero values i.e IN clause is mapping with zero results. If the query gets rows the view works fine.
Corrected Query:
CREATE VIEW open_items_summary AS
SELECT vendor_name, (invoice_total - payment_total - credit_total) AS balance_due
FROM vendors
WHERE balance_due IN
(
-- Query should retrive results to work IN clause properly
SELECT COUNT(balance_due) AS open_item_count,
SUM(balance_due) AS open_item_total
FROM invoices
GROUP BY vendor_name
HAVING balance_due > 0
);