Results 1 to 2 of 2

Thread: subset query

  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Question Unanswered: subset query

    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 ('i3','bread',60,15,'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. #2
    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
  •