Re: he;p with sql+

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 4 Jan 2008 11:46:06 -0800 (PST)
Message-ID: <ef24baf8-1815-486a-bfa6-3cebb2d786d6@j20g2000hsi.googlegroups.com>


On Jan 4, 1:07 pm, Totti <saliba.toufic.geo..._at_gmail.com> wrote:
> David, that was not the same problem this is another one, that is not
> working with the (+) David told me about;even though for the first one
> it worked;
> these data are real, and the way i want them to be is as the first
> table, i gave in the former message, while when using the formula i
> put, i am getting resultsas the second table where all the numbers are
> 497953000 or 0, and this is surely wrong.
>
> **it is not that i dont take advice well, it is only that i am in the
> very first steps of learning SQL, and there may be things that you
> here give but i dont even understand how they work, i need more work
> with SQL, i am not a kind of dumb that doesnt take advices, i m just
> without experience yet. and there are many things i dont know.
>
> Thanks again for your understanding and for your help!

As David mentioned, I attempted to answer your question here (see my last post in that thread):
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/d9f960727d4854f4

I think that I see another problem with your SQL statement: "from purinv, purch,prod,sales"
...
You are selecting rows from 4 tables...

"where purinv.Code = purch.Inv_Code and sales.prod_code = prod.code and purch.Prod_Code in ('SP-20.2', 'SP-20.1', 'SP-20.6')" ...
You are joining:
  purinv to purch
  and sales to prod

You likely need at least one more join condition in the WHERE clause to define how the set (purinv to purch) is related to the set (sales to prod) in order to avoid a Cartesian join between the two data sets. Cartesian join: if there are 100 rows in the first set (purinv to purch) and 100 rows in the second set (sales to prod), you will end up with 10,000 result rows - in your case, this would greatly increase the value returned by SUM, especially if you were expecting only 100 rows in the result set.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jan 04 2008 - 13:46:06 CST

Original text of this message