CODEWITHSUNDEEP
mysqlviewsql-view
LukeS
LukeS

Reputation: 809

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

Thanks for any help!

Upvotes: 3

Views: 7814

Answers (3)

monsur.hoq
monsur.hoq

Reputation: 1131

It should be work as below:

set @StartDate := '2012-01-01';
set @EndDate := '2012-02-01';
select * from Total_Sales_By_Product_Num;

Upvotes: 1

Melanie
Melanie

Reputation: 3111

You need 

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.

Upvotes: 0

LukeS
LukeS

Reputation: 809

Ahhh!!! I got it working, I feel like an idiot but I had double quotes not single quotes in one of my assignments of the dates. It seems to work now. I will leave this post up for future people searching how to do this.

Upvotes: 3

Related Questions