Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00402 Alias required for Select list of cursor to avoid dup col names (2merged)
PLS-00402 Alias required for Select list of cursor to avoid dup col names (2merged) [message #418514] Sun, 16 August 2009 22:27 Go to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Hi Frens,

I encountered the above error and fail to solve.Can someone help me please? I cant seem to trace.

PROCEDURE FTEST_ST_YLD_PROC
(
pScheduleoption IN varchar2,
pCustomercode IN varchar2,
pActivity IN varchar2,
pSTEP IN varchar2,
pWW varchar2,
ppackage IN varchar2,
pCursor IN OUT CRPT_CURTYPE.T_RPTCUR )
IS

vStartDate varchar2(15) default null;
vEndDate varchar2(15) default null;
vWW number default 0;
vCustomercode VarChar2(5);
vPackage VarChar2(30);
vActivity Varchar2(40);
vStep Varchar2(40);

wfr varchar2(20);

Cursor XYZ IS
SELECT leadcount,package,device,testdevice,customercode,originalcustomerlotnumber,customerlotnumber,trackinqty,lotid,bintype,binphysicalqty, testpasssamplenumber,
description,WW,bintype,binnumber,binlotid,t.activity,t.step,month,lastupdate,UNACCOUNTVARIANCE,binlotid,yieldvalue
FROM ftest_testtxn@shdb t, fwcatns_testbintxn@shdb b
where t.lotobject = b.lotobject
and t.step = b.step
and t.operationtype = b.operationtype
and t.lottxncounter = b.lottxncounter
and t.activity = vActivity
and t.customercode =vCustomerCode
and t.step = vStep
and t.package = vPackage
and ((t.lastupdate > vStartDate and t.lastupdate <= vEndDate) or t.ww = vWW);


BEGIN

--CustomerCode
If pCustomercode = '*' OR pCustomercode Is Null Then
vCustomerCode := '%';
Else
vCustomerCode := pCustomercode;
End If;

--Package
If ppackage = '*' OR ppackage Is Null Then
vPackage := '%';
Else
vPackage := ppackage;
End If;

--Actvity
If pActivity = '%' OR pActivity Is Null Then
vActivity := '%';
Else
vActivity := pActivity;
End If;

--Step
If pSTEP = '*' OR pSTEP Is Null Then
vStep := '%';
Else
vStep := pSTEP;
End If;

--Date Range
If pScheduleoption = 'Monthly' then
vStartDate := utl_cal.firstdayofperiod(to_char(SYSDATE,'YYYYMMDD'));
vEndDate := utl_cal.lastdayofperiod(to_char(SYSDATE,'YYYYMMDD'));
elsif pScheduleoption = 'Weekly' then
vWW := utl_cal.workweek(to_char(SYSDATE,'YYYYMMDD'));
vStartDate := firstdayofww(vWW,utl_cal.firstdayofperiod(to_char(SYSDATE,'YYYYMMDD')));
vEndDate := lastdayofww(vStartDate);
elsif pScheduleoption = 'Manual' then
vWW := pWW;
End If;


For Rec in XYZ Loop

select distinct to_char(d.originalcustomerlotnumber) into wfr
from fwcatns_diesinventory d, ftest_testtxn t
where d.lotobject = t.lotobject
and d.customerlotnumber = Lotcur.customerlotnumber;


If wfr is null Then
wfr := Lotcur.originalcustomerlotnumber;
ElsIf length(trim(wfr)) <= 100 Then
wfr := wfr||','||Lotcur.originalcustomerlotnumber;
Else
Exit;
End If;

End Loop;


OPEN PCURSOR FOR
SELECT * FROM ftest_testtxn t, fwcatns_testbintxn b
where t.lotobject = b.lotobject
and t.step = b.step
and t.operationtype = b.operationtype
and t.lottxncounter = b.lottxncounter;

End ;
Re: PLS-00402 Alias required for Select list of cursor to avoid dup col names [message #418515 is a reply to message #418514] Sun, 16 August 2009 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link
Posting Guidelines"
Go to the section labeled "Practice" & do as directed.x

use sqlplus along with CUT & PASTE.

You posted error message & code, but we have no way to know which line of code produced the error.
PLS-00402 Alias required for Select list of cursor to avoid dup col names [message #418516 is a reply to message #418514] Sun, 16 August 2009 22:32 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Hi Frens,

I encountered the above error and fail to solve.Can someone help me please? I cant seem to trace.

PROCEDURE FTEST_ST_YLD_PROC
(
pScheduleoption IN varchar2,
pCustomercode IN varchar2,
pActivity IN varchar2,
pSTEP IN varchar2,
pWW varchar2,
ppackage IN varchar2,
pCursor IN OUT CRPT_CURTYPE.T_RPTCUR )
IS

vStartDate varchar2(15) default null;
vEndDate varchar2(15) default null;
vWW number default 0;
vCustomercode VarChar2(5);
vPackage VarChar2(30);
vActivity Varchar2(40);
vStep Varchar2(40);

wfr varchar2(20);

Cursor XYZ IS
SELECT leadcount,package,device,testdevice,customercode,originalcustomerlotnumber,customerlotnumber,trackinqty,lotid,bintype,binphysicalqty, testpasssamplenumber,
description,WW,bintype,binnumber,binlotid,t.activity,t.step,month,lastupdate,UNACCOUNTVARIANCE,binlotid,yieldvalue
FROM ftest_testtxn@shdb t, fwcatns_testbintxn@shdb b
where t.lotobject = b.lotobject
and t.step = b.step
and t.operationtype = b.operationtype
and t.lottxncounter = b.lottxncounter
and t.activity = vActivity
and t.customercode =vCustomerCode
and t.step = vStep
and t.package = vPackage
and ((t.lastupdate > vStartDate and t.lastupdate <= vEndDate) or t.ww = vWW);


BEGIN

--CustomerCode
If pCustomercode = '*' OR pCustomercode Is Null Then
vCustomerCode := '%';
Else
vCustomerCode := pCustomercode;
End If;

--Package
If ppackage = '*' OR ppackage Is Null Then
vPackage := '%';
Else
vPackage := ppackage;
End If;

--Actvity
If pActivity = '%' OR pActivity Is Null Then
vActivity := '%';
Else
vActivity := pActivity;
End If;

--Step
If pSTEP = '*' OR pSTEP Is Null Then
vStep := '%';
Else
vStep := pSTEP;
End If;

--Date Range
If pScheduleoption = 'Monthly' then
vStartDate := utl_cal.firstdayofperiod(to_char(SYSDATE,'YYYYMMDD'));
vEndDate := utl_cal.lastdayofperiod(to_char(SYSDATE,'YYYYMMDD'));
elsif pScheduleoption = 'Weekly' then
vWW := utl_cal.workweek(to_char(SYSDATE,'YYYYMMDD'));
vStartDate := firstdayofww(vWW,utl_cal.firstdayofperiod(to_char(SYSDATE,'YYYYMMDD')));
vEndDate := lastdayofww(vStartDate);
elsif pScheduleoption = 'Manual' then
vWW := pWW;
End If;


For Rec in XYZ Loop

select distinct to_char(d.originalcustomerlotnumber) into wfr
from fwcatns_diesinventory d, ftest_testtxn t
where d.lotobject = t.lotobject
and d.customerlotnumber = Lotcur.customerlotnumber;


If wfr is null Then
wfr := Lotcur.originalcustomerlotnumber;
ElsIf length(trim(wfr)) <= 100 Then
wfr := wfr||','||Lotcur.originalcustomerlotnumber;
Else
Exit;
End If;

End Loop;


OPEN PCURSOR FOR
SELECT * FROM ftest_testtxn t, fwcatns_testbintxn b
where t.lotobject = b.lotobject
and t.step = b.step
and t.operationtype = b.operationtype
and t.lottxncounter = b.lottxncounter;

End ;
Re: PLS-00402 Alias required for Select list of cursor to avoid dup col names [message #418517 is a reply to message #418516] Sun, 16 August 2009 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Stop spamming this forum.

Do NOT use keywords as column names!
Re: PLS-00402 Alias required for Select list of cursor to avoid dup col names [message #418534 is a reply to message #418516] Mon, 17 August 2009 02:02 Go to previous message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Quote:
Oracle Error :: PLS-00402
alias required in SELECT list of cursor to avoid duplicate column names

Cause
A cursor was declared with a SELECT statement that contains duplicate column names. Such references are ambiguous.

Action
Replace the duplicate column name in the select list with an alias.

You have columns in your select list with the same name. (bintype, and binlotid each occur twice.)
Previous Topic: Comparision Usage of DBLINK & MATERIALIZE VIEW
Next Topic: Tablespace Option in Update and Insert Statement
Goto Forum:
  


Current Time: Sat Dec 03 21:59:49 CST 2016

Total time taken to generate the page: 0.04734 seconds