Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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
Avatar of johnsone
johnsoneFlag of United States of America imageSenior Oracle DBA

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

Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answers