CODEWITHSUNDEEP
sqlplsql
Josh
Josh

Reputation: 3

PL/SQL query error

Trying to fix my PL/SQL query and need some help on what's going wrong. My code is as followed:

DECLARE
   TYPE lv_student_ID IS TABLE OF NUMBER;

BEGIN
   -- assuming you are trying to find all students who have not yet paid, you can use a nested table
   SELECT Lease.StudentIDNumber INTO lv_student_ID
     FROM Lease 
     INNER JOIN Invoice 
     ON Lease.LeaseID = Invoice.LeaseID
    WHERE IsPaid = 'N';

END;

When I run this I get the errors which are as followed:

Error report -
ORA-06550: line 6, column 38:
PLS-00321: expression 'LV_STUDENT_ID' is inappropriate as the left hand side of an assignment statement
ORA-06550: line 7, column 6:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 6, column 4:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Any help would be greatly appreciated!

Upvotes: 0

Views: 835

Answers (2)

rgettman
rgettman

Reputation: 178263

To correct your immediate problem, you must recognize that you're attempting to select a scalar value (a number) into a collection type (a table of number), which doesn't work. To get this working, you first need to declare a variable of that type. Then you can add a bulk collect clause to your query, which changes the semantics of the select into selecting into collection(s).

DECLARE
   TYPE lv_student_ID IS TABLE OF NUMBER;
   student_ids lv_student_id;
BEGIN
    SELECT Lease.StudentIDNumber
    BULK COLLECT INTO student_ids
    FROM Lease 
    INNER JOIN Invoice 
    ON Lease.LeaseID = Invoice.LeaseID
    WHERE IsPaid = 'N';
END;

If you just want the count of students who haven't paid rent, then you can use the .count property of the collection: student_ids.COUNT. But an easier solution would just be to use a simple count(*) aggregate query.

DECLARE
   num_students NUMBER;
BEGIN
   SELECT count(*) INTO num_students
   FROM Lease 
   INNER JOIN Invoice 
   ON Lease.LeaseID = Invoice.LeaseID
   WHERE IsPaid = 'N';
END;

Upvotes: 2

StewS2
StewS2

Reputation: 421

Given that you're trying to count the number of students who haven't paid rent, I would expect you to use the COUNT function. Loading all of them into a TABLE type variable (and as Gordon said, you still need to declare a variable of that type first) would give you a list of student IDs and then you'd have to get their count anyway.

This is fairly basic stuff. Try searching the Oracle documentation for the COUNT function, which will give you a good example.

Upvotes: 0

Related Questions