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

chalie001
chalie001 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

Commented:
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.

Author

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

johnsoneSenior Oracle DBA

Commented:
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.
Ensure you’re charging the right price for your IT

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

Author

Commented:
which original post
johnsoneSenior Oracle DBA

Commented:
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.

Author

Commented:
The error is because am returning 2 rows but I only what to return a second row value
johnsoneSenior Oracle DBA

Commented:
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.

Author

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

Author

Commented:
i remove this  WHERE TO_CODE IS NULL;
johnsoneSenior Oracle DBA

Commented:
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.

Author

Commented:
this si the primary key alter table ITEM_DATA
  add constraint ITEM_DATA_PK primary key (ITM_CODE , accountNr, TO_CODE, accountNrTo);
Senior Oracle DBA
Commented:
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.

Author

Commented:
i have use connect prior

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial