PL/SQL Block Finding number of suppliers for each nation

user_17367582 0
Viewed 47

I'm still new to PLSQL and am currently using TPCH Dataset to practice. I have been trying this for a while not but I can't seem to wrap my head around it and could use some advice. A rough overview of the dataset here.

Here is my code so far

DECLARE
countNationkey number (5);

BEGIN
    FOR QRow IN (   SELECT r_name, n_name, s_nationkey, r_regionkey, count(s_nationkey) INTO countNationkey
            FROM region, nation, supplier
            WHERE r_regionkey = n_regionkey
            AND n_nationkey = s_nationkey
            GROUP BY r_name, n_name, s_nationkey, r_regionkey
            HAVING count(s_nationkey) > 130 
            ORDER BY r_name )

    LOOP
            dbms_output.put_line( rpad('R_NAME', 15) || rpad('N_NAME', 15) || 
                            rpad('COUNT(S_NATIONKEY)', 20) || chr(10) );
            dbms_output.put_line('----------------------------------------------------------'); 
        dbms_output.put_line( rpad(QRow.r_name, 15) || rpad(QRow.n_name, 15) || rpad(countNationkey, 15) );
    END LOOP;
END;

However, when I tried just the select query, I got an error

    SELECT r_name, n_name, s_nationkey, r_regionkey, count(s_nationkey) INTO countNationkey
                                                                         *
ERROR at line 1:
ORA-00905: missing keyword

If I remove the INTO countNationkey, I'm aware that countNationkey has no value thus I will get the output shown below.

R_NAME         N_NAME         COUNT(S_NATIONKEY)  

----------------------------------------------------------
ASIA           CHINA
R_NAME         N_NAME         COUNT(S_NATIONKEY)  

----------------------------------------------------------
ASIA           INDONESIA
R_NAME         N_NAME         COUNT(S_NATIONKEY)  

----------------------------------------------------------
EUROPE         GERMANY
R_NAME         N_NAME         COUNT(S_NATIONKEY)  

----------------------------------------------------------
MIDDLE EAST    SAUDI ARABIA

This is the expected outcome that I want

R_NAME                     N_NAME                    COUNT(S_NATIONKEY)
------------------------- ------------------------- ------------------
ASIA                       INDONESIA                  131
ASIA                       CHINA                      145
MIDDLE                     EAST SAUDI ARABIA          132
EUROPE                     GERMANY                    132

Could really use some help! Thanks in advance!

procedure plsql stored-procedures oracle sql
1 Answers

Just remove INTO. It is required in PL/SQL, but not when select is part of a cursor (in your case, that's a cursor FOR loop).

Also, you'd then reference countNationkey with cursor variable's name (QROW.countNationkey), which also means that you don't need a local variable.

So:

BEGIN
   FOR QRow IN (  SELECT r_name,
                         n_name,
                         s_nationkey,
                         r_regionkey,
                         COUNT (s_nationkey) countNationkey
                    FROM region, nation, supplier
                   WHERE     r_regionkey = n_regionkey
                         AND n_nationkey = s_nationkey
                GROUP BY r_name,
                         n_name,
                         s_nationkey,
                         r_regionkey
                  HAVING COUNT (s_nationkey) > 130
                ORDER BY r_name)
   LOOP
      DBMS_OUTPUT.put_line (
            RPAD ('R_NAME', 15)
         || RPAD ('N_NAME', 15)
         || RPAD ('COUNT(S_NATIONKEY)', 20)
         || CHR (10));
      DBMS_OUTPUT.put_line (
         '----------------------------------------------------------');
      DBMS_OUTPUT.put_line (
            RPAD (QRow.r_name, 15)
         || RPAD (QRow.n_name, 15)
         || RPAD (QROW.countNationkey, 15));
   END LOOP;
END;
user_90979060
Related