Qualifying column names and variable names

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: Mon, 11 May 1998 03:40:06 GMT
Message-ID: <6j5rus$4lr@bgtnsc03.worldnet.att.net>

I have the following table:

SQL> describe employee;

 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPLOYEE_ID                     NOT NULL NUMBER
 EMPLOYEE_NAME                            VARCHAR2(40)
 EMPLOYEE_HIRE_DATE                       DATE
 EMPLOYEE_TERMINATION_DATE                DATE
 EMPLOYEE_BILLING_RATE                    NUMBER

I write the following PL/SQL block:
<<block_label>>
DECLARE
   employee_id number := 110;
BEGIN
   FOR my_employee IN (

      SELECT * 
        FROM employee
       WHERE employee_id = block_label.employee_id

   ) LOOP                DBMS_OUTPUT.PUT_LINE(my_employee.employee_id);    END LOOP;
END; How can I qualify the second reference to "employee_id" in the where clause of the SELECT statement so that it refers to the employee_id that I have declared in my block? As it stands now, "WHERE employee_id = employee_id" looks only at the values in each record. I know that I can label the block, and then write:

      WHERE employee_id = block_label.employee_id

Is there an alternative way, or must I label the block? Received on Sun May 10 1998 - 22:40:06 CDT