OraFAQ Forum: Reports & Discoverer » a table may be outer joined to at most one other table

Home » Developer & Programmer » Reports & Discoverer » a table may be outer joined to at most one other table (Developer 6I Database 10g window xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

a table may be outer joined to at most one other table [message #407910]

Fri, 12 June 2009 05:45

shahzaib_4vip@hotmail.com
Messages: 410
Registered: December 2008
Location: karachi

Senior Member

Dear all

I don't know weather this Question Relate to SQL/PL SQL or Report

Before my question i will like to show my Table Structure

CREATE TABLE d_style (
  d_style_no  VARCHAR2(10),
  description VARCHAR2(10),
  size_1      VARCHAR2(10),
  CONSTRAINT pk_style_no PRIMARY KEY ( d_style_no ))
/

CREATE TABLE color (
  color_code NUMBER(10),
  color_name VARCHAR2(10),
  CONSTRAINT pk_color_name PRIMARY KEY ( color_name ))
/

CREATE TABLE d_stitch (
  d_stitch_style_no VARCHAR2(10),
  stitch_color_name VARCHAR2(10),
  size_1            VARCHAR2(10),
  CONSTRAINT fk_stitch_color_name FOREIGN KEY ( stitch_Color_name ) references color(color_name),
  CONSTRAINT fk_stitch_style_no FOREIGN KEY ( d_Stitch_style_no ) References d_style(d_Style_no))
/

CREATE TABLE d_dispatch (
  d_dispatch_style_no VARCHAR2(10),
  dispatch_color_name VARCHAR2(10),
  size_1              VARCHAR2(10),
  CONSTRAINT fk_dispatch_color_name FOREIGN KEY ( dispatch_Color_name ) references color(color_name),
  CONSTRAINT fk_dispatch_style_no FOREIGN KEY ( d_dispatch_style_no ) References d_style(d_Style_no))
/

Now i am trying to Get Color wise And size wise Report

I need Style_no,Color_name,Stitch_size,Dispatch_size,Return_size And balance (Stitch_size-Dispatch_size+Return_size)

I write this code

But its give me error which is the topic

SELECT   d_style_no,
         color_name,
         d_stitch.size_1                                                                           AS "Stitch Size",
         d_dispatch.size_1                                                                         AS "Dispatch size",
         d_return.size_1                                                                           AS "Return size",
         Sum(Nvl(d_stitch.size_1,0)) - Sum(Nvl(d_dispatch.size_1,0)) + Sum(Nvl(d_return.size_1,0)) AS "Balance"
FROM     d_stitch,
         d_dispatch,
         d_return,
         d_color,
         d_style
WHERE    stitch_color_name (+)  = color_name
         AND dispatch_color_name (+)  = color_name
         AND return_color_name (+)  = color_name
         AND d_dispatch_style_no (+) = d_style.d_style_no
         AND d_return_style_no (+) = d_style.d_style_no
         AND d_stitch.d_stitch_style_no (+) = d_style.d_style_no
GROUP BY d_style_no,
         color_name,
         d_stitch.size_1,
         d_dispatch.size_1,
         d_return.size_1
ORDER BY d_style_no

When i write this code its work fine but those style which come only in d_Stitch table or d_dispatch will not come in Report

SELECT   d_style_no,
         color_name,
         d_stitch.size_1                                                                           AS "Stitch Size",
         d_dispatch.size_1                                                                         AS "Dispatch size",
         d_return.size_1                                                                           AS "Return size",
         Sum(Nvl(d_stitch.size_1,0)) - Sum(Nvl(d_dispatch.size_1,0)) + Sum(Nvl(d_return.size_1,0)) AS "Balance"
FROM     d_stitch,
         d_dispatch,
         d_return,
         d_color,
         d_style
WHERE    stitch_color_name (+)  = color_name
         AND dispatch_color_name (+)  = color_name
         AND return_color_name (+)  = color_name
         AND d_dispatch_style_no = d_style.d_style_no
         AND d_return_style_no = d_style.d_style_no
         AND d_stitch.d_stitch_style_no = d_style.d_style_no
GROUP BY d_style_no,
         color_name,
         d_stitch.size_1,
         d_dispatch.size_1,
         d_return.size_1
ORDER BY d_style_no

Its just Because (+) sign

What can i do

Regards
Shahzaib

Report message to a moderator

Re: a table may be outer joined to at most one other table [message #407921 is a reply to message #407910]

Fri, 12 June 2009 06:22

cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester

Senior Member

The error you got is a standard oracle SQL restriction.
You may be able to get round it by using ansi syntax (I'm not sure about that).

When writting SQL against multiple tables you should always use table aliases for every column. It makes the code so much easier to understand.

If you're missing some data in the report then that's becuase it doesn't match up with the data in the other tables - that's the whole point of outer joins.

Report message to a moderator

Previous Topic:	how to disable the print button in previewer
Next Topic:	wasp128 barcode issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 10:14:27 CST 2024