Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Select statement help
Thanks Ken....that did the trick!
"Ken Denny" <ken_at_kendenny.com> wrote in message
news:Xns92CE8507F3044kendenny_at_65.82.44.10...
> Two things.
>
> 1. In order to get inventory rows which have no matching sales rows you
> need to do an outer join, which is accomplished by using (+) in your where
> clause:
> where inventory.inv_item = sales.item (+)
>
> 2. Since the SALES table contains the column SAL_CUST I assume there will
> be multiple rows with the same INV_ITEM whenever the same item is sold to
> multiple customers. In that case you will need to use the SUM function on
> your sales columns:
> SUM(sales.sal_qty)
>
> "AM" <nospam_at_man.com> wrote in
> news:6_tD9.208332$C8.527646_at_nnrp1.uunet.ca:
>
> > Hi,
> >
> > I have two tables; INVENTORY and SALES. I am trying to come up with a
> > select statement for a report that will show all inventory records.
> > Along with that, also include quantity from the sales table. If there
> > is no sales, have a zero placed in SAL_QTY field. If there is a
> > record, populate SAL_QTY and calculate and populate ON_HAND field.
> >
> >
> > Here is what I got so far:
> >
> > SELECT INVENTORY.*,
> > INVENTORY.INV_QTY - SALES.SAL_QTY AS ONHAND,
> > SALES.SAL_QTY AS Expr1
> > FROM INVENTORY, SALES
> > WHERE INVENTORY.INV_ITEM = SALES.SAL_ITEM
> >
> > The problem is that only records found with matching item numbers are
> > captured. I want all inventory items whether or not they have sales
> > associated with them displayed. Here are the column names:
> >
> >
> > SAL_ITEM
> > SAL_QTY
> > SAL_CUST
> >
> > INV_ITEM
> > INV_DESC
> > INV_TYPE
> > INV_QTY
> > INV_ORDER
> > INV_PRICE
> >
> > Here are my fields for my report.
> > INV_ITEM INV_DESC INV_TYPE INV_QTY INV_PRICE INV_ORDER
> > SAL_QTY ON_HAND
> >
> >
> > Is this even possible??? Any help would be great!
> >
> > Thanks
>
> --
> Ken Denny
> http://www.kendenny.com/
>
Received on Mon Nov 25 2002 - 08:25:52 CST