MySQL Forums
Forum List  »  Stored Procedures

Date Parsing in Stored Procedure
Posted by: thirumurugan desinghu
Date: February 04, 2014 05:46AM

Hi

I am not able to parse date parameters (Fromdate and Todate) from calling procedure to stored procedure definition

Calling Procedure:

call TrackingRegisterReport('Ilmenite',${CountryParam},${PortParam},${CustomerParam},${FromDateParam},${ToDateParam})


Stored Procedure:

Create PROCEDURE TrackingRegisterReport (IN ProductName VARCHAR(255), IN CountryName VARCHAR(255), IN PortName VARCHAR(100), IN CustomerName VARCHAR(255), IN FromDate DATE, IN ToDate DATE)
BEGIN
SET SESSION group_concat_max_len=100000;
SET @cols = (SELECT GROUP_CONCAT('sum(CASE WHEN pr.Grade_Name =','"',Grade_Name,'"',' THEN inv.Inv_Qty END) AS "',Grade_Name SEPARATOR '",') FROM D_ODS_Product where Active='Y' and Product_Name=ProductName order by Grade_Name);
SET @product=ProductName;
SET @country=CountryName;
SET @Customer=CustomerName;
SET @port=PortName;
SET @sql = CONCAT('Select
po.PO_No,
po.PO_Name,
po.PO_Date,
po.PO_Status,
concat(MONTHNAME(po.PO_Date),"-",YEAR(po.PO_Date)) as PO_Month,
po.Delivery_Port,
po.Shipping_Country,
cus.Customer_Name,
pr.Product_Name,
pr.Grade_Name,
sum(po.PO_Qty) as OrderedQty,
sum(inv.FOB_Val) as FOB_Val,
sum(inv.CF_Val) as CFR,
sum(inv.Inv_Qty) as InvoicedQty,
inv.Payment_Terms,
inv.Packing_Type,
cs.Stuffing_Date,
sh.Line_Name as Shipping_Line,
inv.Inv_No,
inv.Inv_Date, ',@cols,'" from
F_ODS_PurchaseOrd po join D_ODS_Customer cus on (po.Customer_ID=cus.D_Customer_ID and po.Active="Y" )
join D_ODS_Product pr on (po.Grade_ID=pr.Grade_ID and po.Product_ID=pr.D_Product_ID)
Join F_ODS_Invoices inv on (po.PO_ID=inv.PO_ID and po.Product_ID=inv.Product_ID and po.Grade_ID=inv.Grade_ID and inv.Active="Y")
Left join F_ODS_ContainerStuff_QC cs on (inv.Inv_No=cs.Invoice_No and po.PO_No=cs.PO_No and po.Product_ID=cs.Product_ID and po.Grade_ID=cs.Grade_ID and cs.Active="Y")
Left join F_ODS_Shipment sh on (sh.ContainerStuffing_ID=cs.ContainerStuffing_ID and sh.Active="Y")
where pr.Product_Name="',@product,'"
and (cus.Customer_Name="',@customer,'" or "ALL"="',@customer,'")
and (po.Shipping_Country="',@country,'" or "ALL"="',@country,'")
and (po.Delivery_Port="',@port,'" or "ALL"="',@port,'")
and date(po.PO_Date) between ',FromDate,' and ',ToDate,'
Group by
pr.Product_Name,
pr.Grade_Name,
po.PO_No,po.PO_Name,po.PO_Date,po.PO_Status,po.PO_Date,po.Delivery_Port,
po.Shipping_Country,cus.Customer_Name,inv.Payment_Terms,
inv.Packing_Type,cs.Stuffing_Date,sh.Line_Name,inv.Inv_No,inv.Inv_Date
order by cus.Customer_Name,pr.Product_Name,pr.Grade_Name,po.PO_Date,inv.Inv_Date,po.PO_Name,inv.Inv_No');
PREPARE stmt FROM @sql;
EXECUTE stmt;
END;


Anyone help me to troubleshoot the issue

Thanks & Regards
Thirumurugan

Options: ReplyQuote


Subject
Views
Written By
Posted
Date Parsing in Stored Procedure
2661
February 04, 2014 05:46AM
960
February 04, 2014 03:18PM
1014
February 05, 2014 08:23PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.