CODEWITHSUNDEEP
sqlplsql
user3487244
user3487244

Reputation: 147

Alias required in SELECT list of cursor to avoid duplicate column names

I am having an issue doing a plsql command. The idea is to show the courier, location, and yes or not if it is over 20.

DECLARE 
 yesnobool VARCHAR(3); 

 CURSOR orders_list IS 
 SELECT shipping.ship_courier, 
 shipping.weightpership,
  shipping.shipping#,
  purchaseorder.shipping#,
  storelocation.location#,
  STORELOCATION.LOC_CITY,
  purchaseorder.location#
 FROM shipping, 
 purchaseorder, 
 storelocation
 WHERE purchaseorder.shipping# = shipping.shipping# 
 and storelocation.location# = purchaseorder.location#;
BEGIN 
 dbms_output.Put_line('Courier || Location || Too Heavy for 1 person lift?'); 
 dbms_output.Put_line('------ -------- -----------------------------------'); 

 FOR an_order IN orders_list LOOP 
 IF SHIPPING.WEIGHTPERSHIP > 20 THEN 
 yesnobool := 'YES'; 
 ELSE 
 yesnobool := 'NO'; 
 END IF; 

 dbms_output.Put_line(Rpad(an_order.ship_courier, 6) || ' ' || 
 Rpad(an_order.loc_city, 8) || ' ' || 
 Rpad(yesnobool, 19)); 
 END LOOP; 
END;

The Error message i am getting is:

Error report - ORA-06550: line 21, column 2: PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names ORA-06550: line 21, column 2: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Im not very good with plsql so i dont really know how to pin down the issue. Thank you in advance for any and all advice.

Upvotes: 3

Views: 27873

Answers (1)

crthompson
crthompson

Reputation: 15865

Columns shipping# and location# are duplicated in your select field list.

shipping.shipping#
purchaseorder.shipping#

storelocation.location#,
purchaseorder.location#

The cursor is unable to distinguish which shipping or location its supposed to use.

The solution is to alias the fields. Something like this:

shipping.shipping# as shipping,
purchaseorder.shipping# as poshipping

And:

storelocation.location# as storeLocation,
purchaseorder.location# as poLocation

Upvotes: 4

Related Questions