CODEWITHSUNDEEP
sqloracle-databaseplsql
MillieJA
MillieJA

Reputation: 31

SELECT COUNT INTO variable

I'm trying to get the row count of a table and store it in a variable so I can use that variable however it will only store it in the variable if I use WHERE ROWNUM = 1; but then it only stores the number 1 rather than the actual number of rows. The whole select statement I'm using is: 

SELECT COUNT(*)
INTO vn_no_tickets
FROM tickets
WHERE ROWNUM = 1;

If I don't use WHERE ROWNUM = 1; I get an error "exact fetch returns more than requested number of rows", but again when I do use it, I get just 1 row not the actual number or rows. Thanks

Edit: Here is the full procedure

CREATE OR REPLACE PROCEDURE proc_ticket_exp IS
    vn_no_tickets NUMBER(4);
    vn_ticket_id tickets.ticket_id%TYPE;
    vd_exp_date tickets.expiration_date%TYPE;
    vc_customer_firstname tickets.customer_firstname%TYPE;
    vc_customer_surname tickets.customer_surname%TYPE;
    vd_one_week DATE;
    vc_fullname VARCHAR2(40); 
BEGIN
    vd_one_week := sysdate + 7;
    SELECT COUNT(*)
    INTO vn_no_tickets
    FROM tickets
    WHERE ROWNUM = 1;
    DBMS_OUTPUT.PUT_LINE (vn_no_tickets);

    WHILE vn_no_tickets != 0 LOOP
        SELECT expiration_date, ticket_id, customer_firstname, customer_surname
        INTO vd_exp_date, vn_ticket_id, vc_customer_firstname,  vc_customer_surname
        FROM tickets
        WHERE ROWNUM = vn_no_tickets;
        vc_fullname := CONCAT(vc_customer_firstname, CONCAT(' ', vc_customer_surname));
        IF vd_exp_date < vd_one_week THEN
            DBMS_OUTPUT.PUT_LINE (vc_fullname || 's ticket will expire within one week. Ticket Number: ' || vn_ticket_id);
        END IF;
        vn_no_tickets := vn_no_tickets - 1;
    END LOOP;        
END proc_ticket_exp;
/ 
SHOW ERRORS

Upvotes: 0

Views: 9412

Answers (2)

psaraj12
psaraj12

Reputation: 5072

Your actual code which is failing is the below since in first query you put rownum=1 you will get count also as 1 hence vn_no_tickets will be 1.If you remove the rownum=1 in first query then vn_no_tickets will be greater than 1 and the below query will fail

    SELECT expiration_date, ticket_id, customer_firstname, customer_surname
    INTO vd_exp_date, vn_ticket_id, vc_customer_firstname,  vc_customer_surname
    FROM tickets
    WHERE ROWNUM <= vn_no_tickets;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The Oracle documentation on ROWNUM is quite explicit:

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT *
FROM employees
WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

(Note: This also applies to an equality comparison higher than 1.)

So, this condition:

    WHERE ROWNUM = vn_no_tickets

only returns a row when vn_no_tickets is "1".

The simplest way to fix this is to dispense with the counter:

FOR t IN (SELECT expiration_date, ticket_id, customer_firstname, customer_surname
         FROM tickets
         )
LOOP
    vc_fullname := t.customer_firstname || ' ' || t.customer_surname;
    IF t.exp_date < vd_one_week THEN
        DBMS_OUTPUT.PUT_LINE (vc_fullname || 's ticket will expire within one week. Ticket Number: ' || t.ticket_id);
    END IF;
END LOOP;

That said, you should move the IF condition into the WHERE clause. It is silly to retrieve a row from the database, just to check a condition -- when that condition can be used to filter the result set in the first place.

Upvotes: 2

Related Questions