ORA-01422: exact fetch returns more than requested number of rows

Hi i have the following function am geting this error when i pass value
CREATE OR REPLACE FUNCTION GEN_CURRENT_AccNo (vsInput VARCHAR2)
   RETURN number
   

IS
   wrk_sin number;


   lsNSN    VARCHAR2 (255);
 
   lnRow    ITEM_BASIC%ROWTYPE;
   lnCode   IITEM_BASIC.SIN%TYPE;
   lnAccnr    ITEM_BASIC.SIN%TYPE;

   c_cnt_acc number(10);





BEGIN
   



  SELECT *
     INTO lnRow
     FROM (SELECT *
             FROM ITEM_BASIC a
            where a.ITM_CODE = regexp_replace(vsInput,'[^[:digit:]]')
           UNION ALL
           SELECT *
             FROM ITEM_BASIC a
            WHERE ITM_CB||ITM_NR||ITM_NAME = vsInput)
    WHERE ROWNUM <= 1;



   IF lnRow.item_type = '9' then
  
      
     SELECT SIN
        INTO lnCode
        FROM (    SELECT *
                    FROM ITEM_DATA
              START WITH ITM_CODE = lnRow.ITM_CODE
              CONNECT BY NOCYCLE PRIOR TO_CODE = ITM_CODE)            
       WHERE TO_CODE IS NULL;

      

   ELSE
      
    lnAccnr := lnRow.ITM_CODE;
 
   END IF;


   RETURN lnAccnr;

  

EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (-20001, SQLERRM);

END;

CREATE TABLE ITEM_DATA
   (ITM_CODE NUMBER(11,0) primary key, 
    accountNr varchar(20),--accountNr=ITM_CB||ITM_NR||ITM_NAME
	TO_CODE NUMBER(11,0), 
	accountNrTo varchar(20),
	ITEM_VERSION VARCHAR2(20 BYTE), 
	CONSTRAINT TO_CODE_UQ UNIQUE (ITM_CODE, TO_CODE));
	 
	 
CREATE TABLE ITEM_BASIC 
   (ITM_CODE NUMBER(11,0), 
	ITM_NAME VARCHAR2(23 BYTE), 
	ITM_CB VARCHAR2(50 BYTE), 
	ITM_NR VARCHAR2(20 BYTE), 
	item_type varchar(2),
	CONSTRAINT IM_CODE_PK PRIMARY KEY (ITM_CODE);
    CONSTRAINT IT_CODE_FK FOREIGN KEY (ITM_CODE)
	REFERENCES ITEM_DATA1 (ITM_CODE) ENABLE;	

Open in new window




in database they will be like this


ITM_CODE   accountNr       TO_CODE     accountNrTo
20751411   3120LB1433679   20751411    3120180573151
20751411   3120180573151   159960150   3120144348210


i what my function to return 159960150 either user pass 20751411 or 3120LB1433679

am in 11g
chalie001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
There are only 2 select statements in your function and the only one that could produce that error is this one.
     SELECT SIN
        INTO lnCode
        FROM (    SELECT *
                    FROM ITEM_DATA
              START WITH ITM_CODE = lnRow.ITM_CODE
              CONNECT BY NOCYCLE PRIOR TO_CODE = ITM_CODE)            
       WHERE TO_CODE IS NULL;

Open in new window

That query returns more than one row.

Your sample data doesn't match with the table structure you supplied.  You are selecting all columns from a table when you really don't need to (you are only using 2).  If ITEM_TYPE = 9 you are returning no data.
0
chalie001Author Commented:
this is the table stucture
CREATE TABLE ITEM_DATA
   (ITM_CODE NUMBER(11,0) not null, 
    accountNr varchar(20) not null,--accountNr=ITM_CB||ITM_NR||ITM_NAME
	TO_CODE NUMBER(11,0) not null, 
	accountNrTo varchar(20) not null,
	ITEM_VERSION VARCHAR2(20 BYTE));
	
	
alter table ITEM_DATA
  add constraint ITEM_DATA_PK primary key (ITM_CODE , accountNr, TO_CODE, accountNrTo);	
	
alter table ITEM_DATA
  add constraint FK_ITEM_DATA_code foreign key (ITM_CODE)
  references ITEM_BASIC (ITM_CODE);
  
alter table ITEM_DATA
  add constraint FK_ITEM_DATA_TO_CODE foreign key (TO_CODE)
  references ITEM_BASIC (ITM_CODE);  

Open in new window

0
johnsoneSenior Oracle DBACommented:
I don't need the table structure to tell you which query causes the issue.

You supply create statements for 2 tables in your original post which contain a total of 10 columns.  Your sample data has 4 columns of data, so even if someone wanted to try to build a test case, it would be impossible.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

chalie001Author Commented:
which original post
0
johnsoneSenior Oracle DBACommented:
The first post in this thread.

Table creates you provided.

CREATE TABLE ITEM_DATA
   (ITM_CODE NUMBER(11,0) primary key,
    accountNr varchar(20),--accountNr=ITM_CB||ITM_NR||ITM_NAME
      TO_CODE NUMBER(11,0),
      accountNrTo varchar(20),
      ITEM_VERSION VARCHAR2(20 BYTE),
      CONSTRAINT TO_CODE_UQ UNIQUE (ITM_CODE, TO_CODE));
             
CREATE TABLE ITEM_BASIC
   (ITM_CODE NUMBER(11,0),
      ITM_NAME VARCHAR2(23 BYTE),
      ITM_CB VARCHAR2(50 BYTE),
      ITM_NR VARCHAR2(20 BYTE),
      item_type varchar(2),
      CONSTRAINT IM_CODE_PK PRIMARY KEY (ITM_CODE);
    CONSTRAINT IT_CODE_FK FOREIGN KEY (ITM_CODE)
      REFERENCES ITEM_DATA1 (ITM_CODE) ENABLE;      

Notice, 10 columns in bold.  This is the sample data provided:

ITM_CODE   accountNr       TO_CODE     accountNrTo
20751411   3120LB1433679   20751411    3120180573151
20751411   3120180573151   159960150   3120144348210

Notice, only 4 columns.

Given that information, how can someone construct meaningful sample data?

Regardless of the confusing samples, you should have the answer to your question as to why you are getting the error.
0
chalie001Author Commented:
The error is because am returning 2 rows but I only what to return a second row value
0
johnsoneSenior Oracle DBACommented:
Let's assume for just a minute that the sample data you provided is 4 of the 5 columns of the ITEM_DATA table.  One would then attempt to create a sample set using the following:
CREATE TABLE ITEM_DATA
   (ITM_CODE NUMBER(11,0) primary key, 
    accountNr varchar(20),--accountNr=ITM_CB||ITM_NR||ITM_NAME
	TO_CODE NUMBER(11,0), 
	accountNrTo varchar(20),
	ITEM_VERSION VARCHAR2(20 BYTE), 
	CONSTRAINT TO_CODE_UQ UNIQUE (ITM_CODE, TO_CODE));
insert into item_data values (20751411,   '3120LB1433679',   20751411,    3120180573151, null);
insert into item_data values (20751411,   '3120180573151',   159960150,   3120144348210, null);
commit;

Open in new window

But, if you try to do that, you will get a constraint violation on the primary key.  If you remove the 2 constraints and then attempt to create the sample set, you would use this:
CREATE TABLE ITEM_DATA
   (ITM_CODE NUMBER(11,0), 
    accountNr varchar(20),--accountNr=ITM_CB||ITM_NR||ITM_NAME
	TO_CODE NUMBER(11,0), 
	accountNrTo varchar(20),
	ITEM_VERSION VARCHAR2(20 BYTE));
insert into item_data values (20751411,   '3120LB1433679',   20751411,    3120180573151, null);
insert into item_data values (20751411,   '3120180573151',   159960150,   3120144348210, null);
commit;

Open in new window

Now, I can get the data into a table, but when I run the offending query:
     SELECT SIN
        INTO lnCode
        FROM (    SELECT *
                    FROM ITEM_DATA
              START WITH ITM_CODE = 20751411
              CONNECT BY NOCYCLE PRIOR TO_CODE = ITM_CODE)            
       WHERE TO_CODE IS NULL;

Open in new window

I get no rows because there isn't a record where TO_CODE is NULL.

So, your sample data isn't valid and doesn't support your problem.  So, how can anyone help you.  This is your data in your system.  You should know the proper constructs, we're guessing.  We can point you in the right direction and with the information provided, I've gone as far as I can.
0
chalie001Author Commented:
this si the test data
Insert into ITEM_DATA (ITM_CODE,ACCOUNTNR,TO_CODE,ACCOUNTNRTO,ITEM_VERSION) values (20751411,' 3120LB1433679 ',20751411,'3120180573151',null);
Insert into ITEM_DATA (ITM_CODE,ACCOUNTNR,TO_CODE,ACCOUNTNRTO,ITEM_VERSION) values (20751411,'3120180573151',159960150,'3120144348210',null);


Insert into ITEM_BASIC (ITM_CODE,ITM_NAME,ITM_CB,ITM_NR,ITEM_TYPE) values (20751411,'0573151','18','3120','9');

Open in new window

0
chalie001Author Commented:
i remove this  WHERE TO_CODE IS NULL;
0
johnsoneSenior Oracle DBACommented:
Again, your test data is invalid.  There is a primary key con ITEM_DATA.ITM_CODE.  Both of your records have the same value, so there is no possible way that this is valid.

Again, only you can answer the question as to how to distinguish between the 2 records and pick the correct one.  We cannot do that.
0
chalie001Author Commented:
this si the primary key alter table ITEM_DATA
  add constraint ITEM_DATA_PK primary key (ITM_CODE , accountNr, TO_CODE, accountNrTo);
0
johnsoneSenior Oracle DBACommented:
That fixes the key issue.  In the first post of this thread, they key is defined as one column.

Until you can figure out what makes the row unique and why you should pick one row over the other, there isn't anything we can help you with.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chalie001Author Commented:
i have use connect prior
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.