CODEWITHSUNDEEP
oracle-databaseplsql
J.J.
J.J.

Reputation: 127

How to join 2 tables in PLSQL in ORACLE APEX

I am trying to create a form with dynamic region for purposes of printing. I have a simple PLSQL that works:

declare

 cursor c_VYDEJKY is
   select ID, INFO, CUSTOMER_ID
     from VYDEJKY
    where ID = :P54_NEW;
 
    begin
      sys.htp.p('<ul>');
      for a in c_VYDEJKY loop
        sys.htp.p('<li>' || a.ID || ' (' || a.CUSTOMER_ID || ')</li>' );
       end loop;
      sys.htp.p('</ul>');
    end;

but when I try to join another table with LEFT JOIN or WHERE clause it doesnt work:

declare

 cursor c_VYDEJKY is
   select v.ID, v.INFO, v.CUSTOMER_ID, c.CUSTOMERNAME
     from VYDEJKY v, CUSTOMERS c
    where v.ID = :P54_NEW
    AND v.CUSTOMER_ID > c.ID;

 
begin
  sys.htp.p('<ul>');
  for a in c_VYDEJKY loop
    sys.htp.p('<li> ID VÝDEJKY  :' ||  a.ID ||'</li>' );
    sys.htp.p('<li> POZNÁMKY  :' ||  a.INFO ||'</li>' );
    sys.htp.p('<li> POZNÁMKY  :' ||  c.CUSTOMERNAME ||'</li>' );
   end loop;
  sys.htp.p('</ul>');
end;

I am getting following error on c.CUSTOMERNAME

ORA-06550: line 15, column 39: PLS-00201: identifier 'C.CUSTOMERNAME' must be declared

Can someone please point to where I am makiing a mistake. I am really just starting with PLSQL.

Thank you, JJ

Upvotes: 0

Views: 220

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132670

Within the for loop, the prefix for the column names refers to the cursor row variable (for a in) not the table alias, so you need to specify:

a.CUSTOMERNAME

Upvotes: 2

Related Questions