asked on 3/8/2017

return value in based on value passed

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);

Open in new window

the pass value is combination of mcr + serial_code which is 12125877887
am in 11g database

Oracle Database

Last Comment

chalie001

3/9/2017

johnsone

3/8/2017

First thing, thank you for posting sample data as SQL statements. Second, when you do that the statements should run and not need to be modified.

First glance at what you want would be this:

SELECT ass_code, 
       ass_quantity, 
       ass_type, 
       end_item_code, 
       nha_code 
FROM   associated_account_item a 
       join account_item_tt b 
         ON a.code = b.code 
            AND b.mcr 
                || b.serial_code = '12125877887';

Open in new window

However, that doesn't give the results you posted.

Why do you need the ACCOUNT_ITEM_WW table? There is only one column in it and it doesn't get you anything (that is why it is left out of the query).

Given your sample data, you need to explain (or even better show) how you get the result you want. Obviously, the simple join that would make sense isn't doing it.

chalie001

3/8/2017

ASKER

i what something like this

select (select bii.mcr||bii.serial_code from account_ITEM_tt bii where bii.code = ii.code)pass_AccNo, (select bii.mcr||bii.serial_code from account_ITEM_tt bii where bii.code = ii.ass_code)ass_accoNr,ii.ass_quantity,ii.ass_type,(select bii.mcr||bii.serial_code from account_ITEM_tt bii where bii.code = ii.end_item_code)end_item_AccNo,(select bii.mcr||bii.serial_code from account_ITEM_tt bii where bii.code = ii.nha_code)nha_AccNo from  ASSOCIATED_Account_ITEM ii;

Open in new window

AccNo.jpg

ASKER CERTIFIED SOLUTION

johnsone

3/8/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

chalie001

3/8/2017

ASKER

i what a parameter and in oracle sql

johnsone

3/8/2017

If you need a where clause, add the join and the where.

SELECT bii.mcr 
       ||bii.serial_code  pass_AccNo, 
       bii2.mcr 
       ||bii2.serial_code ass_accoNr, 
       ii.ass_quantity, 
       ii.ass_type, 
       bii3.mcr 
       ||bii3.serial_code end_item_AccNo, 
       bii4.mcr 
       ||bii4.serial_code nha_AccNo 
FROM   associated_account_item ii 
       join account_item_tt bii 
         ON bii.code = ii.code 
       join account_item_tt bii2 
         ON bii2.code = ii.ass_code 
       join account_item_tt bii3 
         ON bii3.code = ii.end_item_code 
       join account_item_tt bii4 
         ON bii4.code = ii.nha_code 
       join account_item_tt b 
         ON ii.code = b.code 
WHERE  b.mcr 
       || b.serial_code = '12125877887';

Open in new window

Not sure why you can't do that yourself. The query to do that was already posted.

chalie001

3/9/2017

ASKER

join table