troubleshooting Question

inserting value in table in oracle form

Avatar of chalie001
chalie001 asked on
DatabasesOracle DatabaseSQL
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;

  
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 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 2 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