Is it possible to only store a part of my query results in a variable for stored functions?-CodePudding

I'm extremely new to PL/SQL in general and am trying out stored functions and procedures.

I have the below select statement:

SELECT query1.*
  FROM (  SELECT o_custkey, COUNT (o_orderkey) AS ordercount
            FROM orders
        GROUP BY orders.o_custkey) query1,
       (SELECT MAX (query2.ordercount) AS orderhighest
          FROM (  SELECT o_custkey, COUNT (o_orderkey) AS ordercount
                    FROM orders
                GROUP BY orders.o_custkey) query2) query3
 WHERE query1.ordercount = query3.orderhighest;

The goal of this select statement is to count the total number of orders for each customer and find the customer with the highest orders. And the query will return CustKey & the total number of orders that's calculated using the OrderKey.

         O_CUSTKEY ORDERCOUNT
    ---------- ----------
          9787     41

Now I have to find a way to use the Custkey part of the result, insert it into a variable so that I'll be able to use it to query another table for the rest of the customer information.

But I'm not too sure on how to go about doing so. I've heard of using SELECT INTO but I only require the CustKey portion of the query result (i.e. 9787) and not the OrderCount (41).

Edit: I tried changing it into a stored function (with no parameters) based on @Littlefoot's suggestion but I'm not too sure what went wrong:

CREATE OR REPLACE FUNCTION CustWithHighestOrder
    RETURN NUMBER
    
IS
    l_custkey  orders.o_custkey%TYPE;
    
BEGIN
   SELECT query1.o_custkey
     INTO l_custkey
     FROM (  SELECT o_custkey, COUNT (o_orderkey) AS ordercount
               FROM orders
           GROUP BY orders.o_custkey) query1,
          (SELECT MAX (query2.ordercount) AS orderhighest
             FROM (  SELECT o_custkey, COUNT (o_orderkey) AS ordercount
                       FROM orders
                   GROUP BY orders.o_custkey) query2) query3
    WHERE query1.ordercount = query3.orderhighest;
    
    
    
END;

Am I supposed to do a RETURN l_custkey before ending so I can use the variable?

CodePudding user response：

Then don't select * but only what you need.

As it is PL/SQL, declare a local variable and select INTO it.

Such a way presumes that query you posted returns only one row; otherwise, it'll raise too_many_rows (so you'll have to modify it to select into a collection or process data in a loop (which is usually worse option)) or no_data_found (you'll have to handle, somehow).

DECLARE
   l_custkey  orders.o_custkey%TYPE;
BEGIN
   SELECT query1.o_custkey
     INTO l_custkey
     FROM (  SELECT o_custkey, COUNT (o_orderkey) AS ordercount
               FROM orders
           GROUP BY orders.o_custkey) query1,
          (SELECT MAX (query2.ordercount) AS orderhighest
             FROM (  SELECT o_custkey, COUNT (o_orderkey) AS ordercount
                       FROM orders
                   GROUP BY orders.o_custkey) query2) query3
    WHERE query1.ordercount = query3.orderhighest;
END;

CodePudding user response：

From Oracle 12, you can simplify your query to:

SELECT o_custkey
FROM   orders
GROUP BY o_custkey
ORDER BY COUNT(o_orderkey) DESC
FETCH FIRST ROW WITH TIES;

Or, before Oracle 12, you can use:

SELECT o_custkey
FROM   (
  SELECT o_custkey,
         RANK() OVER (ORDER BY COUNT(o_orderkey) DESC) rnk
  FROM   orders
  GROUP BY o_custkey
)
WHERE  rnk = 1;

Now I have to find a way to use the Custkey part of the result, insert it into a variable so that I'll be able to use it to query another table for the rest of the customer information.

Unless the variable is going to be used in multiple places for querying different tables, you probably are best just to incorporate the first query into the second query where you find the additional customer information and do it all within a single query rather than having repeated context-switches between the SQL and PL/SQL scopes.

If you then want to use it in another query then use a JOIN to include it into that query as a sub-query. Something like:

SELECT ot.*
FROM   other_table ot
       INNER JOIN (
         SELECT o_custkey
         FROM   orders
         GROUP BY o_custkey
         ORDER BY COUNT(o_orderkey) DESC
         FETCH FIRST ROW WITH TIES
       ) max_orders
       ON (ot.o_custkey = max_orders.o_custkey)