CODEWITHSUNDEEP
oracle-databaseplsql
BobLoblaw
BobLoblaw

Reputation: 2001

How to use joins in pl/sql?

I'm new to sql and I'm not able to figure out how to use joins where we use aliases with pl/sql variables. This is similiar to what my anonymous block looks like 

DECLARE
 l_count PLS_INTEGER;  
 l_name EmpTable.Name%Type;
BEGIN
   FOR l_row In (
     Select * 
      From User_Tab_Columns
     Where Table_Name = 'EmpTable')
   Loop
     l_name := l_row.Name;
     Select Count(A.l_name)
       INTO l_count
       FROM EmpTable A
      INNER JOIN CustTable B on A.id = B.emp_id;
     DBMS_OUTPUT ("The number of employees who are also customers are " || l_count);
   END LOOP;
END;

I'm getting error as 'l_name' must be declared' and "A"."l_name" invalid identifier. The flow makes perfect sense to me. Is there some nuance that i should know of?

Upvotes: 0

Views: 1294

Answers (1)

cmutt78
cmutt78

Reputation: 859

l_name is a variable and not part of your SQL expression. I'm also more of a fan of using NUMBER vs PLS_INTEGER. I think you would be looking to do something like this:

DECLARE
 l_count NUMBER;
BEGIN
   Select Count(A.Name)
     INTO l_count
     FROM EmpTable A
    INNER JOIN CustTable B on A.id = B.emp_id;
   DBMS_OUTPUT ("The number of employees who are also customers are " || l_count);
END;

Upvotes: 1

Related Questions