Home » SQL & PL/SQL » SQL & PL/SQL » A table may be outer join? (developer 6I window xp database 10g)
A table may be outer join? [message #435975] Sun, 20 December 2009 07:27 Go to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Hi all

I have 2 master table
St_item and St_party

and 2 Detail table
St_opening and st_purchase

i want to sum quantity of purchase and sum quantity of opening which st_opening.item_name=st_item.item_name and
st_opening.party_name=st_party.party_name and
st_purchase.item_name=st_item.item_name and
st_purchase.party_name=st_party.party_name

My code is


Select a.item_no,a.item_name,(sum(nvl(o.qty,0))),(sum(nvl(p.qty,0))) from st_opening o,st_purchase p,st_item a,st_party b
where 
o.item_name(+)=a.item_name and 
o.party_name(+)=b.party_name and
p.item_name(+)=a.item_name and 
p.party_name(+)=b.party_name 



But its given me error

Quote:
A table may be outer joined at most one other table


How can i solved this problem


Regards

Shahzaib
Re: A table may be outer join? [message #435977 is a reply to message #435975] Sun, 20 December 2009 08:04 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
Hi,

your problem is primarily the logical one. Which rows from ST_PARTY table shall be joined to outer joined (= containing NULL) rows from ST_OPENING and ST_PURCHASE? As no column from ST_PARTY is used anywhere except join conditions, you may get rid of it. If you insist on some condition(s) involving this table, you shall firstly specify them in words. Maybe using EXISTS clauses is what you want; but it is hard to tell without knowing the exact requirements on the result set.
Re: A table may be outer join? [message #436127 is a reply to message #435975] Mon, 21 December 2009 12:29 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Thanks for your answer

well i have total 4 Table

st_item
st_party
st_opening
st_purchase


I want sum of st_opening qty where st_opening.item_name=st_item.item_name and st_opening.party_name=st_party.party_name
sum of st_purchase qty where st_purchase.item_name=st_item.item_name
st_purchase.party_name=st_party.party_name

I write this code its working but its given me only 2 record its is because this 2 record are in available in both table st_opening and st_purchase but other which is only insert in st_purchase or st_opening will not coming

My code is

SELECT   a.item_no        ,
         a.item_name      ,
         SUM(NVL(p.qty,0)),
         SUM(NVL(o.qty,0))
FROM     st_item a
         LEFT OUTER JOIN st_party b
         ON       b.party_name=b.party_name
         LEFT OUTER JOIN st_purchase p
         ON       p.item_name =a.item_name
         AND      p.party_name=b.party_name
         LEFT OUTER JOIN st_opening o
         ON       o.item_name               =a.item_name
         AND      o.party_name              =b.party_name
WHERE    COALESCE (p.item_name,o.item_name) =a.item_name
AND      COALESCE(p.party_name,o.party_name)=b.party_name
AND      party_name                         =NVL(:Party_name ,b.party_name)
AND      item_name                          =NVL(:Item_name ,a.item_name)
GROUP BY a.item_no,
         a.item_name


In single table we use (+) for join but its not working in this situation

i hope you guys understand

Regards

Shahzaib
Re: A table may be outer join? [message #436128 is a reply to message #436127] Mon, 21 December 2009 12:37 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want sum of st_opening qty where st_opening.item_name=st_item.item_name and st_opening.party_name=st_party.party_name
sum of st_purchase qty where st_purchase.item_name=st_item.item_name
st_purchase.party_name=st_party.party_name

If this is your requirement, I mean the COMPLETE requirement, why outer join?

Regards
Michel
Re: A table may be outer join? [message #436130 is a reply to message #435975] Mon, 21 December 2009 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: A table may be outer join? [message #436131 is a reply to message #436127] Mon, 21 December 2009 13:46 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
That's all right; anyway did you really understand it?

You are trying to outer join ST_ITEM and ST_PARTY on non-existing rows in ST_OPENING and ST_PURCHASE. Just curious: what shall be the result set for
ST_ITEM having two rows with ITEM_NAMEs 'I1' and 'I2'
ST_PARTY having three rows with PARTY_NAMEs 'P1', 'P2' and 'P3'
ST_OPENING and ST_PURCHASE empty?
How will be the result set changed with changes in ST_PARTY table? What will it be after adding some rows to ST_OPENING and ST_PURCHASE?

I gave you two suggestions. It seems you somehow want to join ST_PARTY, although it is not used anywhere in the query. As you still post implementation instead of result requirements, it is quite impossible to suggest anything more without guessing.
Although, my third thought was, you just misplaced (+) operators in conditions on PARTY_NAME (I would move them to ST_PARTY side); but, as it is equivalent to my first suggestion (get rid of ST_PARTY), this is probably not what you want.

Last comment to the code you posted: I see no relevance to the initial one; especially as the bind variables (:ITEM_NAME, :PARTY_NAME) appeared there.
Re: A table may be outer join? [message #436146 is a reply to message #436131] Mon, 21 December 2009 19:36 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I agree that the proposed join needs to make sense before trying to overcome ORA-01417... That said, the standard way to overcome this is to break it up into separate outer joins. Be sure to test carefully.
SQL>-- create your own test data...
SQL> create table a(id number);

Table created.

SQL> create table b(id number);

Table created.

SQL> create table c(id number);

Table created.

SQL> SELECT a.ID, b.ID, c.ID
  2    FROM a, b, c
  3   WHERE a.ID = b.ID(+)
  4   AND b.ID(+) = c.ID;
 WHERE a.ID = b.ID(+)
            *
ERROR at line 3:
ORA-01417: a table may be outer joined to at most one other table


SQL> SELECT x.a_id, x.b_id
  2    FROM (SELECT a.ID a_id, b.ID b_id
  3            FROM a, b
  4           WHERE a.ID = b.ID(+)) x, c
  5   WHERE x.b_id(+) = c.ID;

no rows selected

SQL>
Re: A table may be outer join? [message #436157 is a reply to message #436146] Mon, 21 December 2009 21:14 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
andrew again wrote on Tue, 22 December 2009 02:36
I agree that the proposed join needs to make sense before trying to overcome ORA-01417... That said, the standard way to overcome this is to break it up into separate outer joins. Be sure to test carefully.

Just curious, is not your example equivalent with INNER JOIN in the subquery (as the join in the main query on B_ID takes only its non-NULL values from the subquery)? After adding this test data
insert into a(id) values(1);
insert into a(id) values(2);
insert into b(id) values(1);
insert into c(id) values(1);
insert into c(id) values(3);
and a slight modification (showing C.ID column), I get
SQL> SELECT x.a_id, x.b_id, c.id
  2     FROM (SELECT a.ID a_id, b.ID b_id
  3             FROM a, b
  4            WHERE a.ID = b.ID(+)) x, c
  5    WHERE x.b_id(+) = c.ID;

      A_ID       B_ID         ID
---------- ---------- ----------
         1          1          1
                               3
Where is the row with A.ID=2? And where should it be and what should be its other figures?

I doubt there is any "standard" way of overcoming this instead of exactly specifying requirements and accordingly resuming (=changing) implementation.
Re: A table may be outer join? [message #436278 is a reply to message #435975] Tue, 22 December 2009 10:57 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
know its difficult to understand what basically my need is so here is my table structure and also my records

CREATE TABLE st_party
             (
                          party_no   VARCHAR2(10),
                          Party_name VARCHAR2(40),
                          CONSTRAINT pk_st_party PRIMARY KEY(party_name)
             )
             / 
CREATE TABLE st_item
             (
                          item_no   VARCHAR2(10),
                          item_name VARCHAR(40) ,
                          CONSTRAINT pk_st_item PRIMARY KEY(item_name)
             )
             / 
CREATE TABLE St_opening
             (
                          dated      DATE                                                                        ,
                          item_name  VARCHAR2(40)                                                                ,
                          party_name VARCHAR2(40)                                                                ,
                          qty        NUMBER(10,2)                                                                ,
                          CONSTRAINT fk_st_opening_item_name FOREIGN KEY(item_name) REFERENCES st_item(item_name),
                          CONSTRAINT fk_st_opening_party_name FOREIGN KEY(party_name) REFERENCES st_party(party_name)
             )
             / 
CREATE TABLE st_purchase
             (
                          dated      DATE                                                                             ,
                          item_name  VARCHAR2(40)                                                                     ,
                          party_name VARCHAR2(40)                                                                     ,
                          qty        NUMBER(10,2)                                                                     ,
                          CONSTRAINT fk_st_purchase_party_name FOREIGN KEY(party_name) REFERENCES st_party(party_name),
                          CONSTRAINT fk_st_purchase_item_name FOREIGN KEY(item_name) REFERENCES st_item(item_name)
             )


The record in this tables

INSERT
INTO   st_party
       (
              party_no,
              party_name
       )
       VALUES
       (
              1,
              'SANA HOB'
       )
       /
INSERT
INTO   st_party
       (
              party_no,
              party_name
       )
       VALUES
       (
              2,
              'ANWAR HOB'
       )
       /
INSERT
INTO   st_item
       (
              item_no,
              item_name
       )
       VALUES
       (
              1,
              'Coat Pant'
       )
       /
INSERT
INTO   st_item
       (
              item_no,
              item_name
       )
       VALUES
       (
              2,
              'Socks'
       )
       /
INSERT
INTO   st_item
       (
              item_no,
              item_name
       )
       VALUES
       (
              3,
              'Shalwar Suit'
       )
       /

INSERT
INTO   st_item
       (
              item_no,
              item_name
       )
       VALUES
       (
              4,
              'Tie'
       )
       /
INSERT
INTO   st_opening
       (
              Dated     ,
              item_name ,
              party_name,
              qty
       )
       VALUES
       (
              '21-dec-09',
              'Socks'    ,
              'SANA HOB' ,
              75
       )
       /
INSERT
INTO   st_opening
       (
              Dated     ,
              item_name ,
              party_name,
              qty
       )
       VALUES
       (
              '21-Jan-09',
              'Tie'      ,
              'SANA HOB' ,
              15
       )
       /
INSERT
INTO   st_opening
       (
              Dated     ,
              item_name ,
              party_name,
              qty
       )
       VALUES
       (
              '26-Dec-09'   ,
              'Shalwar Suit',
              'SANA HOB'    ,
              13
       )
       /
INSERT
INTO   st_purchase
       (
              Dated     ,
              item_name ,
              party_name,
              qty
       )
       VALUES
       (
              '21-dec-09',
              'Socks'    ,
              'SANA HOB' ,
              12
       )
       /

INSERT
INTO   st_purchase
       (
              Dated     ,
              item_name ,
              party_name,
              qty
       )
       VALUES
       (
              '27-dec-09',
              'Coat Pant',
              'ANWAR HOB',
              75
       )
       / /
INSERT
INTO   st_purchase
       (
              Dated     ,
              item_name ,
              party_name,
              qty
       )
       VALUES
       (
              '27-dec-09',
              'Socks'    ,
              'SANA HOB' ,
              21
       )
       /
INSERT
INTO   st_purchase
       (
              Dated     ,
              item_name ,
              party_name,
              qty
       )
       VALUES
       (
              '27-dec-09',
              'Socks'    ,
              'SANA HOB' ,
              17
       )
       /
INSERT
INTO   st_Opening
       (
              Dated     ,
              item_name ,
              party_name,
              qty
       )
       VALUES
       (
              '27-dec-09',
              'Socks'    ,
              'SANA HOB' ,
              8
       )
       /
INSERT
INTO   st_Opening
       (
              Dated     ,
              item_name ,
              party_name,
              qty
       )
       VALUES
       (
              '27-dec-09',
              'Socks'    ,
              'SANA HOB' ,
              13
       )



Now i required

Item no----Item_name----Sum(opening_qty)----Sum(purchase_qty)

Which is

2----Socks----50----96

My code is

SELECT   a.item_no        ,
         a.item_name      ,
         SUM(NVL(p.qty,0)),
         SUM(NVL(o.qty,0))
FROM     st_item a
         LEFT OUTER JOIN st_party b
         ON       b.party_name=b.party_name
         LEFT OUTER JOIN st_purchase p
         ON       p.item_name =a.item_name
         AND      p.party_name=b.party_name
         LEFT OUTER JOIN st_opening o
         ON       o.item_name               =a.item_name
         AND      o.party_name              =b.party_name
WHERE    COALESCE (p.item_name,o.item_name) =a.item_name
AND      COALESCE(p.party_name,o.party_name)=b.party_name
AND      party_name                         =NVL(:Party_name ,b.party_name)
AND      item_name                          =NVL(:Item_name ,a.item_name)
GROUP BY a.item_no,
         a.item_name



I hope you understand

Regards

shahzaib ismail
Re: A table may be outer join? [message #436279 is a reply to message #436278] Tue, 22 December 2009 11:06 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
So now, you have no problem with "missing" rows in outer join?
Only with multiplication of SUMs, when there are multiple rows in in ST_OPENING and ST_PURCHASE for one row in ST_ITEM?
Then, please read this thread, there is similar problem desscribed with its correct solution: http://www.orafaq.com/forum/m/435808/96705/#msg_435808
Re: A table may be outer join? [message #436629 is a reply to message #435975] Thu, 24 December 2009 11:19 Go to previous message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Finally i done it

using this

Select item_no,item_name,sum(nvl(o.oqty,0)),sum(nvl(p.pqty,0)) from st_item,
(select item_name,sum(nvl(qty,0))oqty from st_opening where party_name=nvl(:Party_from ,party_name) group by item_name)o,
(select item_name,sum(nvl(qty,0))pqty from st_purchase where party_name=nvl(:Party_from ,party_name) group by item_name)p
where 
o.item_name=st_item.item_name and 
p.item_name=st_item.item_name 
group by st_item.item_name 




Thankyou guys

Regards

Shahzaib ismail
Previous Topic: ORA-12015: cannot create a fast refresh materialized view ..
Next Topic: compare TWO CLOB column in two table (merged)
Goto Forum:
  


Current Time: Sun Sep 25 21:29:09 CDT 2016

Total time taken to generate the page: 0.04503 seconds