I need to create a scalar-valued function named fnUnpaidInvoiceID that returns t

Question

I need to create a scalar-valued function named fnUnpaidInvoiceID that returns the InvoiceID of the earliest invoice with an unpaid balance. Test the function in the following SELECT statement: SELECT VendorName, InvoiceNumber, InvoiceDueDate, InvoiceTotal - CreditTotal - PaymentTotal AS Balance FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceID = dbo.fnUnpaidInvoiceID()

Explanation / Answer

BEFORE BRAIN WAS PUT INTO GEAR: CREATE FUNCTION fnUnpaidInvoiceID() RETURNS INT AS BEGIN RETURN (SELECT TOP 1 InvoiceID FROM Invoices WHERE (InvoiceTotal - CreditTotal - PaymentTotal) > 0 ORDER BY InvoiceDate) END Use the FUNCTION called fnUnpaidInvoiceID() in the below T-SQL statement: SELECT VendorName, InvoiceNumber, InvoiceDueDate, InvoiceTotal - PaymentTotal - CreditTotal AS Balance FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceID = dbo.fnUnpaidInvoiceID() AFTER BRAIN WAS PUT INTO GEAR: -- 2. Create a scalar-valued function named fnUnpaidInvoiceID -- that returns the InvoiceID of the earliest invoice with an -- unpaid balance. Test the function in the following SELECT -- statement: CREATE FUNCTION fnUnpaidInvoiceID() RETURNS INT WITH EXECUTE AS CALLER AS BEGIN DECLARE @InvoiceID INT; SET @InvoiceID = (SELECT TOP 1 InvoiceID FROM Invoices WHERE (InvoiceTotal - CreditTotal - PaymentTotal) > 0 ORDER BY InvoiceDate); RETURN (@InvoiceID); END -- Use the FUNCTION called fnUnpaidInvoiceID(). USE AP GO SELECT VendorName, InvoiceNumber, InvoiceDueDate, InvoiceTotal - PaymentTotal - CreditTotal AS Balance FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceID = dbo.fnUnpaidInvoiceID()

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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