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;
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);
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;
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;
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;
I get no rows because there isn't a record where TO_CODE is NULL.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
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.