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  |
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 ;
|
|
|
|
|
|
| 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  |
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 #418534 is a reply to message #418516] |
Mon, 17 August 2009 02:02 |
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.)
|
|
|
|
Goto Forum:
Current Time: Wed Aug 06 08:02:58 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
