asked on
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 12125877887ASKER
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;
ASKER
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';
Not sure why you can't do that yourself. The query to do that was already posted.
ASKER
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
TRUSTED BY
First glance at what you want would be this:
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.