troubleshooting Question

return value in based on value passed

Avatar of chalie001
chalie001 asked on
Oracle Database
6 Comments1 Solution147 ViewsLast Modified:
hi i what to return value based on value passed
i what query to display like

user pass AccNumber  12125877887

ASS_code     ASS_QUANTITY ASS_TYPE                   END_ITEM_code          NHA_code
1317872222   500                      H                                   1416022554     1318756999

test data is
create table account_ITEM_tt
(
  code          NUMBER(11) not null,
  serial_code       VARCHAR2(20) not null,
  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) values (5890851,'125877887','12');
  Insert into account_ITEM_tt (code,serial_code,mcr) values (6853822,'17872222','13');
  Insert into account_ITEM_tt (code,serial_code,mcr) values (7857003,'16022554','14');
  Insert into account_ITEM_tt (code,serial_code,mcr) values (8146674,'18756999','13');
  
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);
  
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_EISs
  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);
  
the pass value is  combination of mcr + serial_code which is 12125877887
am in 11g database
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 6 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 6 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