DA Morgan wrote:
>
> C Chang wrote:
>
> > DA Morgan wrote:
> > >
> > > C Chang wrote:
> > >
> > > > DA Morgan wrote:
> > > > >
> > > > > C Chang wrote:
> > > > >
> > > > > > I figured out the following way to my IN list in the explicit
> > > > > > cursor:
> > > > > >
> > > > > > Cursor c_qty ( p_siteID IN varchar2, p_vendorID IN varchar2) is
> > > > > > SELECT SUM(r.qty), r.qty_unit
> > > > > > FROM receipts r, lines p, heads h
> > > > > > WHERE r.site_id= p_siteID
> > > > > > AND h.vendor_id IN (select vendor_id
> > > > > > from vendor v,
> > > > > > (select db_num
> > > > > > from vendor
> > > > > > where vendor_id = p_vendorID ) d
> > > > > > where v.db_num = d.db_num)
> > > > > > AND 'other conditions'
> > > > > > GROUP BY r.qty_unit, r.site_id;
> > > > > >
> > > > > > But the problem is will it be too resource extensive if this cursor been
> > > > > > called in another dynamic cursor loop for 8K times?
> > > > > >
> > > > > > C Chang
> > > > >
> > > > > I don't get the point of your nested subselects in the WHERE clause. You are
> > > > > selecting from vendor and the joining it to another select from the same
> > > > > table. Not knowing your schema I am wondering why you aren't doing it in one
> > > > > step.
> > > > >
> > > > > Daniel Morgan
> > > > Thanks Dan. My Vendor table has records like:
> > > >
> > > > id name dbNo
> > > > ---- ------- ------
> > > > a abba 123
> > > > b abbb 124
> > > > c abbc 125
> > > > d abbd 123
> > > > ..
> > > > The ID column is unique and the other two are not. I want to use the
> > > > dbNo to select out what ID can be with the same dbNo and use the result
> > > > as the condition in main select query. Thanks for your suggest from
> > > > other post. We tried to upgrade to 9i ( at least 8.1.7) for almost a
> > > > year. Becasue I am not oracle "Expert" (not DBA, not oracle programmer,
> > > > just a "casual" developer), not enough to persude our Manager to make
> > > > the decision.
> > > >
> > > > C Chang
> > >
> > > Still confused about what you are trying to do ... can you (without snipping any
> > > of this) post a sample result set from your demo data.
> > >
> > > Daniel Morgan
> > Thanks again for your kindness help. The result of my subquery will be
> > like
> > ID
> > --
> > a
> > d
> >
> > if the the p_vendorID of the subquery is a or b which both has the same
> > dbNo.
> > The bigger picture is that there are a receipt table, a purchase
> > order table and a purchase heading table ( in the main select section
> > ). PO heading is composed with vendor_ID, and PO header .., and
> > Purchase Order contains the detail of PO, such as different line item
> > (line number), name of item (a unique ID), qty, unit... And the receipt
> > consists name of the item, qty, unit, vendor ID, po header and line
> > number. What the cursor try to do is to find out the total qty of the
> > same item from many differnt orders but the same COMPANY ( supposed to
> > be the same vendor ID). But somehow, the same COMPANY changed their
> > vendor ID from shipment to shipment for the same item, which has a
> > unique number. That's why the cursor can not use the h.vendor_id =
> > p_vendorID . I hope this will clear out a little. I will print out
> > the real query of the subset and the main cursor. If you need more, I
> > also can print the whole procedure of the package. Sincerely appreciate
> > for a learning experience.
> >
> > C chang
>
> The solution is likely simple but your example doesn't correspond with your SQL
> statement.
>
> If you start with:
>
> > > id name dbNo
> > > ---- ------- ------
> > > a abba 123
> > > b abbb 124
> > > c abbc 125
> > > d abbd 123
>
> and your desired result set is this:
>
> ID
> --
> a
> d
>
> Then the SQL statement is:
>
> SELECT DISTINCT id
> FROm table
> WHERE dbNo = '123'
>
> Yet I suspect that isn't what you want. A clear demonstration of table data and result
> would help. Strive to elminiate ambiguity.
>
> Daniel Morgan
Thanks, Daniel, for your patient. We are snow in today at home, can't
get to the data source. I will reply to you when I got the full picture
of the data.
C Chang
Received on Sun Feb 16 2003 - 11:44:11 CST
