Suppose I have a view that rolls up data via a GROUP BY
clause:
CREATE VIEW InvoiceAmountsByCustomer AS
SELECT
Customer.CustomerID,
SUM(Invoice.TotalAmount) AS TotalInvoiceAmount
FROM
Customer LEFT OUTER JOIN Invoice ON Customer.CustomerID = Invoice.CustomerID
GROUP BY Customer.CustomerID
Now, suppose I want the view to restrict which invoices are included in the total based on a date range. To illustrate (pretending {From Date}
and {To Date}
contain the date range):
CREATE VIEW InvoiceAmountsByCustomer AS
SELECT
Customer.CustomerID,
SUM(Invoice.TotalAmount) AS TotalInvoiceAmount
FROM
Customer LEFT OUTER JOIN Invoice ON Customer.CustomerID = Invoice.CustomerID
WHERE
Invoice.Date BETWEEN {From Date} AND {To Date}
GROUP BY Customer.CustomerID
What do I replace {From Date}
and {To Date}
with so that I can pass their values (directly or indirectly) to the view? For example, is it possible to associate these values with the current session?
CREATE VIEW InvoiceAmountsByCustomer AS
SELECT
Customer.CustomerID,
SUM(Invoice.TotalAmount) AS TotalInvoiceAmount
FROM
Customer LEFT OUTER JOIN Invoice ON Customer.CustomerID = Invoice.CustomerID
WHERE
Invoice.Date BETWEEN GetUserDefinedSessionValue('From Date') AND GetUserDefinedSessionValue('To Date')
GROUP BY Customer.CustomerID
where GetUserDefinedSessionValue
is an imaginary function that returns values associated with the current session. Another imaginary function, SetUserDefinedSessionValue
would be called by the client prior to querying the view:
SetUserDefinedSessionValue('From Date', ...)
SetUserDefinedSessionValue('To Date', ...)
SELECT * FROM InvoiceAmountsByCustomer
My imaginary example is only meant to illustrate one way I envision these values being passed to the view.
Note: This is a trivial example and the real situation is much more complex, preventing me from executing a GROUP BY
query directly by the client.