Post reply

create a view named open_items_summary

  • June 23, 2010 at 4:57 pm

    #100267

    I need help with the following and after I run the script, it gives an error.

    create a view named open_items_summary that returns one summary row for each vendor that contains invoices with unpaid balance dues.

    each row should include:

    vendor_name

    open_item_count(the number of invoices with a balance due)

    open_item_total (the total of balance due amounts)

    and the row should be sorted by

    open_item_totals DESC

    CREATE OR REPLACE VIEW open_items_summary AS

    SELECT vendor_name

    COUNT(*) AS open_item_count

    SUM(invoice_total - payment_total - credit_total) AS open_item_total_sum

    FROM vendors

    JOIN invoices

    ON vendors.vendor_id = invoices.vendor_id

    ORDER BY open_item_total DESC

  • June 24, 2010 at 1:42 am

    #1184356

    Looks like your view definition comes from an Oracle port, isn't it?

    This should do the trick for you:

    CREATE VIEW open_items_summary

    AS SELECT vendor_name,

    COUNT(*) AS open_item_count,

    SUM(invoice_total - payment_total - credit_total) AS open_item_total_sum

    FROM vendors

    JOIN invoices

    ON vendors.vendor_id = invoices.vendor_id

    As far as the order by is concerned, usually it isn't allowed in views. You could use TOP 100 PERCENT in the SELECT cluase to add an order by. Be warned that this works in SQLServer 7 and 2000 but it's undocumented and unguaranteed. You should order your data outside the view, not inside the view.

    -- Gianluca Sartori

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply