1. Registered User
Join Date
Jan 2006
Posts
2

Consider an item relation in grochery shop.

item relation :

CREATE TABLE item
( itemid VARCHAR2(9),
name VARCHAR2(10),
weight NUMBER,
price NUMBER,
type VARCHAR2(11),
CONSTRAINT table1_pk PRIMARY KEY (itemid),
CONSTRAINT table1_ck CHECK (type IN ('Eatable', 'Non-Eatable')))
/
INSERT ALL
INTO item VALUES ('i1','soap',40,20,'Non-Eatable')
INTO item VALUES ('i2','facepowder',250,70,'Non-Eatable')
INTO item VALUES ('i4','ToothPaste',150,50,'Non-Eatable')
INTO item VALUES ('i5','jam',35,65,'Eatable')
INTO item VALUES ('i6','chips',20,50,'Eatable')
INTO item VALUES ('i7','hairOil',60,100,'Non-Eatable')
INTO item VALUES ('i8','Perfume',100,35,'Non-Eatable')
SELECT * FROM DUAL
;

I want to perform this operator :
DEFINITION of CROSS UNION operator:
The cross union operator takes as input 2 sets of subsets and gives as output the union of every pair of subsets, each one taken from each of the inputs.
example:
let there are two sets of subsets w1 = {{i1,i3,i5,i7},{i5,i7,i8}} , w2 = {{i1,i3,i4,i5,i7},{i4,i5,i7,i8}}
if we operate cross union operator, the result is {{i1,i3,i4,i5,i7},{i1,i3,i4,i5,i7,i8},{i4,i5,i7,i8 }}.

I created 2 view ; subitem1 and subitem2. :

subitem1 view :

CREATE OR REPLACE VIEW subitem1 AS
SELECT itemids,names,tot_price,tot_weight,rownum AS sid
FROM (SELECT itemids,names,prices,weights,
CALCULATE_NUMERIC_EXPRESSION (prices) AS tot_price,
CALCULATE_NUMERIC_EXPRESSION (weights) AS tot_weight
FROM (SELECT SUBSTR (SYS_CONNECT_BY_PATH (itemid, ','), 2) AS itemids,
SUBSTR (SYS_CONNECT_BY_PATH (name, ','), 2) AS names,
SUBSTR (SYS_CONNECT_BY_PATH (price, '+'), 2) AS prices,
SUBSTR (SYS_CONNECT_BY_PATH (weight, '+'), 2) AS weights
FROM item
CONNECT BY itemid > PRIOR itemid))
WHERE tot_price = 200 AND tot_weight= 195
ORDER BY (sid)
/

subitem2 view :

CREATE OR REPLACE VIEW subitem2 AS
SELECT itemids,names,tot_price,tot_weight,rownum AS sid
FROM (SELECT itemids,names,prices,weights,
CALCULATE_NUMERIC_EXPRESSION (prices) AS tot_price,
CALCULATE_NUMERIC_EXPRESSION (weights) AS tot_weight
FROM (SELECT SUBSTR (SYS_CONNECT_BY_PATH (itemid, ','), 2) AS itemids,
SUBSTR (SYS_CONNECT_BY_PATH (name, ','), 2) AS names,
SUBSTR (SYS_CONNECT_BY_PATH (price, '+'), 2) AS prices,
SUBSTR (SYS_CONNECT_BY_PATH (weight, '+'), 2) AS weights
FROM item
CONNECT BY itemid > PRIOR itemid))
WHERE tot_price = 250 AND tot_weight=345
ORDER BY (sid)
/

I have created this query :
SELECT itemid,id1,id2,sid_s1,sid_s2
FROM item,
(SELECT s1.itemids AS id1,s2.itemids AS id2, s1.sid AS sid_s1,s2.sid AS sid_s2
FROM subitem1 s1, subitem2 s2 )
WHERE INSTR (id1,itemid) > 0 OR INSTR (id2,itemid) > 0
ORDER BY (sid_s1),sid_s2
/

The result is :

ITEMID ID1 ID2 SID_S1 SID_S2
--------- -------------------- -------------------- ---------- ----------
i1 i1,i3,i5,i7 i1,i3,i4,i5,i7 1 1
i3 i1,i3,i5,i7 i1,i3,i4,i5,i7 1 1
i4 i1,i3,i5,i7 i1,i3,i4,i5,i7 1 1
i5 i1,i3,i5,i7 i1,i3,i4,i5,i7 1 1
i7 i1,i3,i5,i7 i1,i3,i4,i5,i7 1 1

i1 i1,i3,i5,i7 i4,i5,i7,i8 1 2
i3 i1,i3,i5,i7 i4,i5,i7,i8 1 2
i4 i1,i3,i5,i7 i4,i5,i7,i8 1 2
i5 i1,i3,i5,i7 i4,i5,i7,i8 1 2
i7 i1,i3,i5,i7 i4,i5,i7,i8 1 2
i8 i1,i3,i5,i7 i4,i5,i7,i8 1 2

i1 i5,i7,i8 i1,i3,i4,i5,i7 2 1
i3 i5,i7,i8 i1,i3,i4,i5,i7 2 1
i4 i5,i7,i8 i1,i3,i4,i5,i7 2 1
i5 i5,i7,i8 i1,i3,i4,i5,i7 2 1
i7 i5,i7,i8 i1,i3,i4,i5,i7 2 1
i8 i5,i7,i8 i1,i3,i4,i5,i7 2 1

i4 i5,i7,i8 i4,i5,i7,i8 2 2
i5 i5,i7,i8 i4,i5,i7,i8 2 2
i7 i5,i7,i8 i4,i5,i7,i8 2 2
i8 i5,i7,i8 i4,i5,i7,i8 2 2

21 rows selected.

My question is, How to eleminate of the {i1,i3,i4,i5,i7,i8} duplicate ???

2. Registered User
Join Date
May 2004
Location
Dominican Republic
Posts
721
Where is i1,i3,i4,i5,i7,i8 set in the output ?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•