Source of the hit on a concatenated datastore? [message #310230] |
Mon, 31 March 2008 11:21 |
redonisc
Messages: 20 Registered: March 2008 Location: Guatemala, C.A.
|
Junior Member |
|
|
Hi, i'm having trouble with a scenario where i have a concatenated datastore(reference http://forums.oracle.com/forums/thread.jspa?threadID=635914&tstart=0), this is what i've tried, with few problems, i've to use a union any way?
What i've is this, a table buyer_tbl that is basically a table where a buyer is registered, now this table have a child table with a few alias names of the buyer buyer_branch_tbl. and a equal scenario this one as suppliers supplier_tbl->supplier_branch_tbl all this trying to create a concat index with field "name" of the 4 tables.
Now the only thing could be equal on the 2 groups is the field NIT that's a simple identifier key this could be the same on the tables buyer_tbl, supplier_tbl, now i'm working on a text search that output this:
Nit Name Source of Record
===========================
1234 abc Supplier
1235 bcasdf Buyer
The real problem is the field Source or Record= this is the group where record was found, now either on buyers or supplier.
Code:
SCOTT@orcl_11g> CREATE TABLE buyer_tbl
2 (buyer_id NUMBER,
3 name VARCHAR2 (5),
4 nit VARCHAR2 (5),
9 CONSTRAINT buyer_id_pk PRIMARY KEY (buyer_id))
10 /
SCOTT@orcl_11g> CREATE TABLE buyer_branch_tbl
2 (buyer_id NUMBER, branch_id NUMBER,
3 name VARCHAR2 (5),
9 CONSTRAINT buyer_branch_id_pk PRIMARY KEY (buyer_id, branch_id))
10 /
SCOTT@orcl_11g> CREATE TABLE supplier_tbl
2 (supplier_id NUMBER,
3 name VARCHAR2 (5),
4 nit VARCHAR2 (5),
9 CONSTRAINT supplier_id_pk PRIMARY KEY (supplier_id))
10 /
SCOTT@orcl_11g> CREATE TABLE supplier_branch_tbl
2 (supplier_id NUMBER, branch_id NUMBER,
3 name VARCHAR2 (5),
9 CONSTRAINT supplier_branch_id_pk PRIMARY KEY (supplier_id, branch_id))
10 /
SCOTT@orcl_11g> ALTER TABLE buyer_brach_tbl ADD (
2 CONSTRAINT FK_buyer_tbl
3 FOREIGN KEY (buyer_id)
4 REFERENCES buyer_tbl (buyer_id)); /
SCOTT@orcl_11g> ALTER TABLE supplier_brach_tbl ADD (
2 CONSTRAINT FK_supplier_tbl
3 FOREIGN KEY (supplier_id)
4 REFERENCES supplier_tbl (supplier_id)); /
[Updated on: Mon, 31 March 2008 11:34] Report message to a moderator
|
|
|