I am trying to write a query to \"Write a subquery to return one row per vendor,

Question

I am trying to write a query to "Write a subquery to return one row per vendor, representing the vendor’s earliest invoice_due_date. Each row should include ( vendor name, vendor_id, count of invoices, the earliest invoice due date. Filter the result set to only show the rows with more than 3 count of invoices." This is my query so far

select i.vendor_name, i.vendor_id, count(invoice_id) as number_of_invoices, min(invoice_due_date) as earliest_due_date

from vendors i join

(select vendor_id, count(invoice_id) as number_of_invoices, min(invoice_due_date) as earliest_due_date

from invoices

group by vendor_id

having count(invoice_id) > 3) v

on i.VENDOR_ID = v.VENDOR_ID

group by i.VENDOR_ID, i.vendor_name;

I get this error "

ORA-00904: "INVOICE_DUE_DATE": invalid identifier

00904. 00000 - "%s: invalid identifier"

*Cause:   

*Action:

Error at Line: 1 Column: 81"

NOTE: the vendor name column belongs to the vendors table and the vendor_id is the primary key in the vendors table and a foreign key in the invoices table. All other columns belong to invoices table.

Explanation / Answer

Answer : Try this is the modified query it will sure because you are missing the alias name for the INVOICE Table that is 'V'.

select i.vendor_name, i.vendor_id, count(invoice_id) as number_of_invoices, min(invoice_due_date) as earliest_due_date
from vendors i join
(select v.vendor_id, count(invoice_id) as number_of_invoices, min(invoice_due_date) as earliest_due_date
from invoices v
group by vendor_id
having count(invoice_id) > 3) v
on i.VENDOR_ID = v.VENDOR_ID
group by i.VENDOR_ID, i.vendor_name;

Thanks...

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.