Convert MS SQL Function to Oracle Function Throws Errors

Question

I'm trying to convert a MS SQL Function to Oracle Function, but I'm getting errors and I cannot understand what errors or how to fix them.

Can anyone help:

CREATE OR REPLACE FUNCTION ItemsSold 
(
    p_PrdID nvarchar2(50),  
    p_Cst nvarchar2(50)
)
RETURN Number
AS
 v_Price NUMBER(18,2);
BEGIN

 SELECT (Min(s.Price)*i.Qty) AS MinP into v_Price
FROM            Customers AS c INNER JOIN
                         CustOrders AS o ON c.Name = o.Cust INNER JOIN
                         Sales AS i ON i.Order = o.Order INNER JOIN
                         Purchases AS s ON i.Item = s.Item
WHERE        i.Item = p_PrdID AND o.Cust = p_Cst
group by i.Qty)

    RETURN v_Price;



END;

Errors

Error(4,23): PLS-00103: Encountered the symbol "(" when expecting one of the following:     := . ) , @ % default character The symbol ":=" was substituted for "(" to continue. 
Error(6,18): PLS-00103: Encountered the symbol "(" when expecting one of the following:     := . ) , @ % default character The symbol ":=" was substituted for "(" to continue. 
Error(17,29): PLS-00103: Encountered the symbol "INNER" when expecting one of the following:     , ; for group having intersect minus order start union where    connect

no semi colon after group by i.Qty)

kevinskio
– kevinskio

2016-04-27 19:37:47 +00:00
Commented Apr 27, 2016 at 19:37 — kevinskio
– kevinskio, Commented Apr 27, 2016 at 19:37

user5683823 · Accepted Answer · 2016-04-27 19:59:30Z

4

The open-parenthesis errors have to do with the variable declarations - in PL/SQL it is not permitted to assign a size to variables (so let them be NVARCHAR2, you can't say NVARCHAR2(50)).

The third error has to do with Oracle syntax for table name aliases - Oracle uses the key word AS for column aliases (optional), but does not permit its use for table name aliases.

Remove the size specification for NVARCHAR2 for the variables and the key word AS before the alias c (on the lines indicated by the error messages) and see what happens.

Good luck!

answered Apr 27, 2016 at 19:59

user5683823

Sign up to request clarification or add additional context in comments.

3 Comments

kevinskio Over a year ago

arrgh, missed the obvious issues you saw, oh well, many eyes is a better solution

Peter Taylor Over a year ago

Will test in next 30 mins and then mark answer, that's for replies

Jens Krogsboell Over a year ago

And replace "group by i.Qty)" with "group by i.Qty;"

Collectives™ on Stack Overflow

Convert MS SQL Function to Oracle Function Throws Errors

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related