Need a MySQL workaround to pass a variable \ parameter into view?
I would like to pass a variable\parameter into a view to filter by a date range. Since I am doing grouping in the view I can not put the where clause externally to the view.
I have read a bunch of posts on this but have not been able to get it working yet. Most workaround I have seen to pass a parameter into a view involve using a function to return the value and this is where I am getting stuck.
/* My current view that I want to pass in start and end dates */
CREATE VIEW Total_Sales_By_Product_Num AS
SELECT products_all_fields.*,
Sum(<a bunch of code removed for this sample>) AS Item_Qty_Requested_Total,
Sum(<a bunch of code removed for this sample>) AS Item_Qty_Accepted_Total,
Sum(<a bunch of code removed for this sample>) AS Total_Sales_Dollars
FROM products_all_fields
LEFT OUTER JOIN (web_orders_items
INNER JOIN web_orders_header
ON web_orders_items.Web_Order_Num = web_orders_header.Web_Order_Num )
ON products_all_fields.Product_Num = web_orders_items.Product_Num
WHERE web_orders_header.Order_Date BETWEEN fn_GetStartDate() AND fn_GetEndDate()
GROUP BY products_all_fields.Product_Num
ORDER BY products_all_fields.Description
The functions to return the parameters to the view:
CREATE FUNCTION fn_GetStartDate ()
RETURNS DATE
DETERMINISTIC NO SQL
BEGIN
RETURN @StartDate;
END
CREATE FUNCTION fn_GetEndDate ()
RETURNS DATE
DETERMINISTIC NO SQL
BEGIN
RETURN @EndDate;
END
This does not work, it returns no data, I know I am doing something stupid
set @fn_StartDate := '2012-01-01';
set @fn_EndDate := '2012-02-01';
select * from Total_Sales_By_Product_Num
SET @fn_StartDate = fn_GetStartDate('2012-01-01')
same with @fn_EndDate
and then in your view
WHERE web_orders_header.Order_Date BETWEEN @fn_StartDate.....
However, that's just the syntax of how to call a function. You're still trying to pass a parameter to a view, which is not possible, as far as I know. Try putting all your View logic in the function instead.
sumber:http://stackoverflow.com/questions/14511760/need-a-mysql-workaround-to-pass-a-variable-parameter-into-view
I would like to pass a variable\parameter into a view to filter by a date range. Since I am doing grouping in the view I can not put the where clause externally to the view.
I have read a bunch of posts on this but have not been able to get it working yet. Most workaround I have seen to pass a parameter into a view involve using a function to return the value and this is where I am getting stuck.
The functions to return the parameters to the view:
This does not work, it returns no data, I know I am doing something stupid
|
No comments:
Post a Comment