Link to home
Start Free TrialLog in
Avatar of chalie001
chalie001

asked on

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
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of chalie001
chalie001

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
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i what a parameter and in oracle sql
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.
join table