Home » Developer & Programmer » Reports & Discoverer » a table may be outer joined to at most one other table (Developer 6I Database 10g window xp)
a table may be outer joined to at most one other table [message #407910] Fri, 12 June 2009 05:45 Go to next message
shahzaib_4vip@hotmail.com
Messages: 376
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

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 Go to previous message
cookiemonster
Messages: 12403
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.
Previous Topic: how to disable the print button in previewer
Next Topic: wasp128 barcode issue
Goto Forum:
  


Current Time: Sat Dec 03 08:09:47 CST 2016

Total time taken to generate the page: 0.10797 seconds