Click here to Skip to main content
13,771,476 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I would like to how to create Function with Start and End Date Parameter.Please check below query and advise how to create this function....


CREATE OR REPLACE FUNCTION foo(_StartDate date, _EndDate date)
Begin
Select T.Company_Name As Buyer,O.Product_Desc As Article,P.Colour_Desc As Color,A.Quantity As Pieces,A.Area,GetOrigin_Detail(A.Stock_Id)As Origin,L.Internal_Batch_No As JobCardNo, Transaction_Date As IssueDate,C.Stock_Type_Desc As StockSection,U.Thickness_Value As SubStance, fnlotref(A.Stock_Id)As LotRef,_100280.getreceipt_grade(A.stock_id) AS receiptgrade, _100280.getreceipt_size(A.stock_id) AS receiptsize, V.Selection_Value As Selection
from stock_transaction_detail_101 A
Join Stock_Details B On (B.Stock_Id = A.Stock_Id)
Join Stock_Type C On (C.Stock_Type_Id = B.Stock_Type_Id)
Join Master_Stock_Category D On D.Stock_Category_Id = B.Stock_Category_Id
Join Stock_Details_101 E On (E.Stock_Id = B.Stock_Id)
Join Master_Size F On (F.Size_Id = E.Size_Id)
Left Join Stock_Price G On (G.Stock_Id = B.Stock_Id)
Join Master_Product_101 H On (H.Product_Id = E.Product_Id)
Join Master_Animal I On I.Animal_Id = H.Animal_Id
Join Batch_Status_Stock J On (J.Stock_Id = A.Reference_Detail)
Join Order_Status_Batches K On (K.Status_Id = J.Batch_Status_Id)
Join Order_Details_Batches L On (L.Batch_Id = K.Batch_Id)
Join Order_Details_items M On (M.Order_Id = L.Order_Id) And (M.Item_Id = L.Item_Id)
Left Join Master_order_type N On N.Order_Type_Id `enter code here`= M.Order_Type_Id
Join Master_Product O On (O.Product_Id = M.Product_Id)
Join Master_Colour P On (P.Colour_Id = M.Colour_Id)
Join Order_Details Q On (Q.Order_Id = M.Order_Id)
Join Relationships R On (R.RelationShip_Id = Q.Relationship_Id)
Join Master_Member S On (S.Member_Id = K.Partner_Id)
Join Master_member T On (T.Member_Id = R.Partner_Id)
Left Join Master_thickness U ON U.Thickness_Id = M.Thickness_Id
JOIN sys_master_selection_106 V ON V.selection_id = E.selection_id
--Where (A.Transaction_Date Between '2017-12-01' And '2017-12-31') And (A.Transaction_Type = 'o') And (A.Stock_Reference_Type_Id = 3)
Where (A.Transaction_Date Between '2017-03-03' And '2017-03-03') And (A.Transaction_Type = 'o') And (A.Stock_Reference_Type_Id = 3)
Union All

Select T.Company_Name As Buyer,O.Product_Desc As Article,P.Colour_Desc As Color,A.Quantity As Pieces,A.Area,GetOrigin_Detail(A.Stock_Id) As Origin,L.Internal_Batch_No As JobCardNo,Transaction_Date As IssueDate ,C.Stock_Type_Desc As StockSection,U.Thickness_Value As SubStance, fnlotref(A.Stock_Id)As LotRef,_100280.getreceipt_grade(A.stock_id) AS receiptgrade, _100280.getreceipt_size(A.stock_id) AS receiptsize, NULL As Selection
from stock_transaction_detail_106 A
Join Stock_Details B On (B.Stock_Id = A.Stock_Id)
Join Stock_Type C On (C.Stock_Type_Id = B.Stock_Type_Id)
Join Master_Stock_Category D On D.Stock_Category_Id = B.Stock_Category_Id
Join Stock_Details_106 E On (E.Stock_Id = B.Stock_Id)
Join Master_Size F On (F.Size_Id = E.Size_Id)
Left Join Stock_Price G On (G.Stock_Id = B.Stock_Id)
Join Master_Product H On (H.Product_Id = E.Product_Id)
Join Master_Animal I On I.Animal_Id = H.Animal_Id
Join Batch_Status_Stock J On (J.Stock_Id = A.Reference_Detail)
Join Order_Status_Batches K On (K.Status_Id = J.Batch_Status_Id)
Join Order_Details_Batches L On (L.Batch_Id = K.Batch_Id)
Join Order_Details_items M On (M.Order_Id = L.Order_Id) And (M.Item_Id = L.Item_Id)
Left Join Master_order_type N On N.Order_Type_Id = M.Order_Type_Id
Join Master_Product O On (O.Product_Id = M.Product_Id)
Join Master_Colour P On (P.Colour_Id = M.Colour_Id)
Join Order_Details Q On (Q.Order_Id = M.Order_Id)
Join Relationships R On (R.RelationShip_Id = Q.Relationship_Id)
Join Master_Member S On (S.Member_Id = K.Partner_Id)
Join Master_member T On (T.Member_Id = R.Partner_Id)
Left Join Master_thickness U ON U.Thickness_Id = M.Thickness_Id
Where (A.Transaction_Date Between '2017-03-03' And '2017-03-03') And (A.Transaction_Type = 'o') And (A.Stock_Reference_Type_Id = 3)

End;
$$
LANGUAGE plpgsql;

What I have tried:

I have above query but not solved.....
Posted 3-Jan-18 0:09am

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05 | 2.8.181119.1 | Last Updated 3 Jan 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100