Why is this function not being created?

Question

Trying to create a function that takes in a month(1-12) and returns the productID that has the highest sales(qtyordered * quoted price). The error: PL/SQL: ORA-00936: missing expression, coming from the sub-query in the where clause. I'm assuming that's the only error with this function, but I can't seem to get this sub-query to work.

New to SQL so I apologize if this is something super obvious. Using Oracle.

CREATE OR REPLACE FUNCTION get_monthly_sales (month_num IN NUMBER)
RETURN NUMBER IS 
    p_id NUMBER(10,0) := 0;
BEGIN 
    SELECT productid INTO p_id
    FROM order_details ods INNER JOIN orders o
        ON ods.orderid = o.id
    WHERE (SELECT MAX(SUM(qtyordered * quotedprice)) FROM order_details)
    AND EXTRACT(MONTH FROM orderdate) = month_num;
RETURN p_id;
END;

EDIT: I added the IN keyword to the WHERE clause, which allowed the function to be created but now returning the following error: ORA-00978: nested group function without GROUP BY.

WHERE p_id IS 
        (SELECT MAX(SUM(qtyordered * quotedprice)) FROM order_details)
    AND EXTRACT(MONTH FROM orderdate) = month_num;

Answer 1

Try this

  Select productid INTO p_id 
    from(SELECT productid 

   FROM order_details ods INNER JOIN 
    orders o
    ON ods.orderid = o.id

 WHERE 
EXTRACT(MONTH FROM orderdate) 
= month_num 
order by (qtyordered * quotedprice)
Desc) where rownum=1

Answer 2

You're correct that your problem is in the WHERE clause. However, I can't figure out what you're trying to do so it's difficult to advise you.

You have

WHERE (SELECT MAX(SUM(qtyordered * quotedprice)) FROM order_details) ...

The problem is that (SELECT MAX(SUM(qtyordered * quotedprice)) FROM order_details) is not a boolean expression - it can't stand by itself in a WHERE clause. Furthermore, the use of the SUM function implies that you're trying to group something, because SUM (as it appears here) is a grouping function - but you don't have a GROUP BY expression in your subquery, nor does it seem to make much sense to have one.

Also, I'm not sure you really want to pass in MONTH_NUM as a parameter and use it the way you are. Let's say you pass in 4 as the MONTH_NUM, meaning "April". The way you've written your function (assuming the query works) you'd gather together data for April 2018, April 2019, April 2020, etc, etc. I doubt you wanted that. Perhaps you want to pass in a date, and truncate it to the month level. So perhaps you're looking for

CREATE OR REPLACE FUNCTION GET_MONTHLY_SALES(pin_Month IN DATE)
  RETURN NUMBER
IS
  nTotal_monthly_sales  NUMBER; 
BEGIN 
  SELECT SUM(od.QTYORDERED * od.QUOTEDPRICE)
    INTO nTotal_monthly_sales
    FROM ORDER_DETAILS od
    INNER JOIN ORDERS o
      ON o.ID = od.ORDERID
    WHERE TRUNC(o.ORDERDATE, 'MONTH') = TRUNC(pin_Month, 'MONTH');

  RETURN nTotal_monthly_sales;
END GET_MONTHLY_SALES;

That should (if I've guessed which tables the various fields are on correctly) should get you the total sales for the month corresponding to the input date, which seems to correspond with the name of the function.

EDIT

Based on your description of the problem the solution should be something along the lines of

CREATE OR REPLACE FUNCTION FIND_PRODUCT_WITH_MAX_SALES(pinMonth IN NUMBER)
  RETURN NUMBER
IS
BEGIN
  IF pinMonth BETWEEN 1 AND 12 THEN
    FOR aRow IN (SELECT od.PRODUCT_ID,
                        SUM(od.QTYORDERED * od.QUOTEDPRICE) AS TOTAL_MONTHLY_SALES
                   FROM ORDER_DETAILS od
                   INNER JOIN ORDERS o
                     ON o.ID = od.ORDERID
                   WHERE TO_NUMBER(TO_CHAR(o.ORDERDATE, 'MM')) = pinMonth
                   GROUP BY od.PRODUCT_ID
                   ORDER BY SUM(od.QTYORDERED * od.QUOTEDPRICE) DESC)
    LOOP
      RETURN aRow.TOTAL_MONTHLY_SALES;
    END LOOP;
  ELSE
    RAISE_APPLICATION_ERROR(-20001, 'FIND_PRODUCT_WITH_MAX_SALES: Error - pinMonth (' || 
                                    pinMonth || ') not in range 1..12'); 
  END IF;
END FIND_PRODUCT_WITH_MAX_SALES;