troubleshooting Question

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

Avatar of chalie001
chalie001 asked on
DatabasesOracle DatabaseMongoDBSQL
13 Comments1 Solution632 ViewsLast Modified:
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;	



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
ASKER CERTIFIED SOLUTION
johnsone
Senior Oracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros