DECLARE
  o_id    Order_Items.Order_ID%TYPE;
  i_id    NUMBER(10);
  qun     Order_Items.Quantity%TYPE;
  u_price Order_Items.Unit_Price%TYPE;
  total   INT;

  CURSOR Orders IS SELECT * FROM Order_Items;    
BEGIN
  total := u_price * qun;
  SELECT Order_Items.order_id, COUNT(item_id) item_count, SUM(unit_price * quantity) total
    INTO o_id, i_id, total
    FROM Order_items
   GROUP BY order_id
  HAVING SUM(unit_price * quantity) > 500000 
     AND COUNT(item_id) BETWEEN 10 AND 12
   ORDER BY total DESC, item_count DESC;

  dbms_output.put_line(LPAD('-', 60, '-'));
  dbms_output.put_line(RPAD('Order ID', 20) || RPAD('Item Count', 20) ||RPAD('Total', 20));
  dbms_output.put_line(LPAD('-', 60, '-'));
  dbms_output.put_line(RPAD(o_id, 20) || RPAD(i_id, 20) || RPAD(total, 20));
  dbms_output.put_line(LPAD('-', 60, '-'));

END;
/

Using the HAVING clause I wrote a PL/SQL code to find orders whose values are greater than 500,000 and the number of products in each order is between 10 and 12.

But, I got ORA-01422 error. What could be the problem ?

CodePudding user response：

You have more than 1 different "order_id" in your "Order_items", so you can't fetch the results into variables: you have to LOOP or BULK COLLECT into a table of records.