[BUG] Procedure not converting properly using Ora2pg 24.1 (MSSQL Server - Postgresql)

[SaravananNewt]

Hi

I am experiencing issues converting the procedure DDLS from MSSQL Server to PostgreSQL using Ora2pg 24.1. The procedure DDLS are not converting properly.

2. I have attached the procedure DDLS of MSSQL Server and the converted Ora2pg procedure DDLS.
3. Could you please provide a proper fix for this issue?

MSSQL Server Procedure DDLS

CREATE PROCEDURE [dbo].[CustOrderHist] @CustomerID nchar(5) AS SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C 
WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName

CREATE PROCEDURE [dbo].[CustOrdersDetail] @OrderID int AS SELECT ProductName, UnitPrice=ROUND(Od.UnitPrice, 2), Quantity, Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2) FROM Products P, [Order Details] Od WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

CREATE PROCEDURE [dbo].[CustOrdersOrders] @CustomerID nchar(5) AS SELECT OrderID, OrderDate, RequiredDate, ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID

create procedure [dbo].[Employee Sales by Country] @Beginning_Date DateTime, @Ending_Date DateTime AS SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount FROM Employees INNER JOIN (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) ON Employees.EmployeeID = Orders.EmployeeID WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

create procedure [dbo].[Sales by Year] @Beginning_Date DateTime, @Ending_Date DateTime AS SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

CREATE PROCEDURE [dbo].[SalesByCategory] @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998' AS IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' BEGIN SELECT @OrdYear = '1998' END SELECT ProductName, TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0) FROM [Order Details] OD, Orders O, Products P, Categories C WHERE OD.OrderID = O.OrderID AND OD.ProductID = P.ProductID AND P.CategoryID = C.CategoryID AND C.CategoryName = @CategoryName AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear GROUP BY ProductName ORDER BY ProductName

create procedure [dbo].[Ten Most Expensive Products] AS SET ROWCOUNT 10 SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice FROM Products ORDER BY Products.UnitPrice DESC USE [master] ALTER DATABASE [Northwind] SET  READ_WRITE

Ora2pg Coversion (Procedure DDLs)

CREATE OR REPLACE PROCEDURE dbo.custorderhist (p_CustomerID char(5)) AS $body$
BEGIN
 ProductName, Total=SUM(Quantity)
FROM Products P, Order Details OD, Orders O, Customers C
WHERE C.CustomerID = p_CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
;
$body$
LANGUAGE PLPGSQL
;

CREATE OR REPLACE PROCEDURE dbo.custordersdetail (p_OrderID integer) AS $body$
BEGIN
 ProductName,
    UnitPrice=round((Od.UnitPrice)::numeric, 2),
    Quantity,
    Discount=CAST(Discount * 100 AS integer), 
    ExtendedPrice=ROUND(TO_CHAR(Quantity * (1 - Discount) * Od.UnitPrice), '')::money
FROM Products P, Order Details Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = p_OrderID
;
$body$
LANGUAGE PLPGSQL
;

CREATE OR REPLACE PROCEDURE dbo.custordersorders (p_CustomerID char(5)) AS $body$
BEGIN
 OrderID, 
    OrderDate,
    RequiredDate,
    ShippedDate
FROM Orders
WHERE CustomerID = p_CustomerID
ORDER BY OrderID
;
$body$
LANGUAGE PLPGSQL
;

CREATE OR REPLACE PROCEDURE dbo.employee (Sales by Country' p_Beginning_Date timestamp(3) without time zone, p_Ending_Date timestamp(3) without time zone) AS $body$
BEGIN
 Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, 'Order Subtotals'.Subtotal AS SaleAmount
FROM Employees INNER JOIN(Orders INNER JOIN 'Order Subtotals' ON Orders.OrderID = 'Order Subtotals'.OrderID) 
    ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between p_Beginning_Date And p_Ending_Date
;
$body$
LANGUAGE PLPGSQL
;

CREATE OR REPLACE PROCEDURE dbo.sales (by Year' p_Beginning_Date timestamp(3) without time zone, p_Ending_Date timestamp(3) without time zone) AS $body$
BEGIN
 Orders.ShippedDate, Orders.OrderID, 'Order Subtotals'.Subtotal, date_part('year',ShippedDate) AS Year
FROM Orders INNER JOIN 'Order Subtotals' ON Orders.OrderID = 'Order Subtotals'.OrderID
WHERE Orders.ShippedDate Between p_Beginning_Date And p_Ending_Date
;
$body$
LANGUAGE PLPGSQL
;

CREATE OR REPLACE PROCEDURE dbo.salesbycategory (p_CategoryName varchar(4), p_OrdYear varchar(4) = '1998') AS $body$
BEGIN
 p_OrdYear != '1996' AND p_OrdYear != '1997' AND p_OrdYear != '1998' 
BEGIN
    SELECT p_OrdYear = '1998'
END

SELECT ProductName,
    TotalPurchase=ROUND(SUM(TO_CHAR(2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), '')::decimal(14
FROM Order Details OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID 
    AND OD.ProductID = P.ProductID 
    AND P.CategoryID = C.CategoryID
    AND C.CategoryName = p_CategoryName
    AND SUBSTRING(TO_CHAR(O.OrderDate, ' yyyy/mm/dd')::nvarchar(22), 1, 4) = p_OrdYear
GROUP BY ProductName
ORDER BY ProductName
;
$body$
LANGUAGE PLPGSQL
;

CREATE OR REPLACE PROCEDURE dbo.ten (Most Expensive Products') AS $body$
BEGIN
 ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
;
$body$
LANGUAGE PLPGSQL
;

[SaravananNewt]

Ora2pg team - Can I receive some help with my raised issue?

[darold]

Commits 5ee236b and 9995693 might solve the problem except for the procedure with space in the name which can not be supported by Ora2Pg.