troubleshooting Question

inserting value in table in oracle form

Avatar of chalie001
chalie001 asked on
SQLDatabasesOracle Database
2 Comments1 Solution460 ViewsLast Modified:
hi i what to insert value in my screen like this
IN my screen the use will enter value like this
the user will pass this value passed for the screen to appear 1234-12-125-8328 its code is 5890851


Associted_Account Ass_quantity Ass_type    End_item_Code                  Nha_code
8745155258732            23        A                     9874166787831                  3748177602234

8745-15-525-8732 9874-16-678-7831

after saving the value should display like this

Associted_Account Ass_quantity Ass_type    End_item_Code        Nha_code
8745-15-525-8732            23        A                  9874-16-678-7831    3748-17-760-2234


am only saving in one table ASSOCIATED_Account_ITEM after saving the value will be like o r the value which will be inserted ill be like

Insert into ASSOCIATED_Account_ITEM (code,ASS_code,ASS_QUANTITY,ASS_TYPE,END_ITEM_code,NHA_code)  values(5890851,1890855,500,'D',2853826,3857007);


this is my test data
create table account_ITEM_tt  
(  
  code          NUMBER(11) not null,  
  serial_code       VARCHAR2(20) not null,  
  nrm          varchar(10),
  mcr          VARCHAR2(2) default '7' not null  
  
);  
alter table account_ITEM_tt  
  add constraint code_ITEM_PK primary key (code);  
 -- accNo= mcr + serial_code  
    
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (5890851,'1258328','12',1234);  
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (6853822,'1734937','13',9887);  
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (7857003,'1602830','14',987);  
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (8146674,'1875331','13',2344);  
   5325-14-242-9490 accNo = nrm+mcr+serial_code 8745-15-525-8732
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (1890855,'5258732','15',8745);  
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (2853826,'6787831','16',9874);  
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (3857007,'7602234','17',3748);  
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (4146678,'8875635','18',9847);

9874-16-678-7831 


create table account_ITEM_ww  
(  
  code                 NUMBER(11) not null  
    
);  
alter table account_ITEM_ww  
  add constraint code_ITEM_ww_PK primary key (code);  
    
  alter table account_ITEM_ww  
  add constraint FK_ww_code foreign key (code)  
  references account_ITEM_tt (code)  
  deferrable initially deferred;  
Insert into account_ITEM_ww (code)values(5890851);    
Insert into account_ITEM_ww (code)values(6853822);    
Insert into account_ITEM_ww (code)values(7857003);    
Insert into account_ITEM_ww (code)values(8146674);  

Insert into account_ITEM_ww (code)values(1890855);    
Insert into account_ITEM_ww (code)values(2853826);    
Insert into account_ITEM_ww (code)values(3857007);    
Insert into account_ITEM_ww (code)values(4146678);  
    
create table ASSOCIATED_Account_ITEM  
(  
  code          NUMBER(11) not null,  
  ass_code      NUMBER(11) not null,  
  ass_quantity NUMBER(11,2),  
  ass_type     VARCHAR2(1),  
  end_item_code NUMBER(11),  
  nha_code      NUMBER(11)  
);  
  
  
alter table ASSOCIATED_Account_ITEM  
  add constraint PK_ASS_ITEM primary key (code,ass_code);  
    
  alter table ASSOCIATED_Account_ITEM  
  add constraint CHK_ASS_ASS_QTY  
  check (ass_quantity  > 0);  
    
alter table ASSOCIATED_Account_ITEM  
  add constraint CHK_ASS_ASS_code_EIS  
  check (ass_code <> end_item_code);  
alter table ASSOCIATED_Account_ITEM  
  add constraint CHK_ASS_ASS_code_NHA  
  check (ass_code <> nha_code);  
alter table ASSOCIATED_Account_ITEM  
  add constraint CHK_ASS_code_ASS_code  
  check (code <> ass_code);  
alter table ASSOCIATED_Account_ITEM  
  add constraint CHK_ASS_code_EIS  
  check (code <> end_item_code);  
alter table ASSOCIATED_Account_ITEM  
  add constraint FK_ASS_CII_ASS_code foreign key (ASS_code)  
  references account_ITEM_ww (code);  
    
alter table ASSOCIATED_Account_ITEM  
  add constraint FK_ASS_CII_END_ITEM_code foreign key (END_ITEM_code)  
  references account_ITEM_ww (code);  
    
alter table ASSOCIATED_Account_ITEM  
  add constraint FK_ASS_CII_NHA_code foreign key (NHA_code)  
  references account_ITEM_ww (code);  
    
alter table ASSOCIATED_Account_ITEM  
  add constraint FK_ASS_CII_code foreign key (code)  
  references account_ITEM_ww (code);  
  
  
  Insert into ASSOCIATED_Account_ITEM (code,ASS_code,ASS_QUANTITY,ASS_TYPE,END_ITEM_code,NHA_code)  values(5890851,6853822,500,'D',7857003,8146674);

i pre-insert trigger like this
decalre
cursor get_code is
         select ww.code from ccount_ITEM_ww ww,account_ITEM_tt tt
              where nrm = substr(:blockName.global_code,1,4)
               and  mcr = substr(:blockname.global_code,5,2);
               and  serial_code = substr(:blockname.globalcode,10,4)
               and ww.code = tt.code;


cursor get_ass_code is
         select ww.code from ccount_ITEM_ww ww,account_ITEM_tt tt
              where nrm = substr(:blockName.ass_code,1,4)
               and  mcr = substr(:blockname.ss_code,5,2);
               and  serial_code = substr(:blockname.ass_code,10,4)
               and ww.code = tt.code;


cursor get_nha_code is
         select ww.code from ccount_ITEM_ww ww,account_ITEM_tt tt
              where nrm = substr(:blockName.nha_code,1,4)
               and  mcr = substr(:blockname.nha_code,5,2);
               and  serial_code = substr(:blockname.nha_code,10,4)
               and ww.code = tt.code;


cursor get_end_code is
         select ww.code from ccount_ITEM_ww ww,account_ITEM_tt tt
              where nrm = substr(:blockName.endcode_code,1,4)
               and  mcr = substr(:blockname.end_code,5,2);
               and  serial_code = substr(:blockname.end_code,10,4)
               and ww.code = tt.code;



begin

--- how can i put those cursor in one palce so i can do this
   
	for curr in get_code loop
                    :blockname.codevalue := curr.applicode;
          end loop;
  
         for curr in get_ass_code loop
                    :blockname.ass_code_ent := curr.code;
          end loop;


        for curr in get_nha_code loop
                    :blockname.nhacode_ent := curr.code;
          end loop;


         for curr in get_end_code loop
                    :blockname.end_code_ent := curr.code;
          end loop;




end;

  

Open in new window

ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 2 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 2 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004