Question of my explicit cursor with a subquery

C Chang wrote:
>
> 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

Dan:

   Here is the correct result of the cursor without GROUP(qty).

PO_ID		LINE	NSN			QTY	UI  VENDOR	DATE		RCVD ID
-------    ----   ------------     ----   --- ------  -----      
-------
CP12607	4	5310009618393	20	EA	HON	9/21/00	CPR9484
CP12807	7	5310009618393	40	EA	HON	10/13/00	CPR11902
CP13240	7	5310009618393	50	EA	HON	12/5/00	CPR17477
CP13587	12	5310009618393	25	EA	HON	1/19/01	CPR21202
CP13617	6	5310009618393	200	EA	HON	1/23/01	CPR21581
CP14425	6	5310009618393	200	EA	HON	4/9/01	CPR31419
CP20333	15	5310009618393	25	EA	AS	5/7/02	CPR74769

If you notice that the Vendor include both 'HON' and 'AS' because they represent the same company who produces the same 'NSN'. The above records are from a receipts table. In the meantime, we have a contract table that include the signed qty of the NSN with various company. In that table, all the contracted company, with its Vendor, are unique.

Vendor      Company          			Qty   	NSN
------      -------                      ----        ------
AS		Honeywell/Alied signal        1000		5310009618393
WES         WestCo			      500         5310009618393

In order to figure out how many of the contracted Qty have been consumed,
So we build the third table which includes all the information of the comapny such as

Vendor  	Company          			DB_NUM
-------    	--------                      -------
HON         Honeywell               	10099            <--  both HON ans
AS are the same company
AS          Honeywell/Alied Signal        10999            <--
WES		WestCo				10998

So if we do no use the IN condition in the cursor to include both AS and HON, I will only get Qty of 25 rather 560. on the other hand if I do not use the h.vendor_ID IN condition, the return total Qty will cover the qty that does not belong to the 'AS' or 'HON', such as those from 'WES'. As the query result of the procedure will look like

Vendor	NSN	   		C'Qty    Qty Used	  Percentage	  Since Date
------     ----- 	  		----     --------    -----------      -----------
 AS     	5310009618393     1000		560	    56%               01/01/00

And what concern me is that the whole procedure is designed to return 8000 records similar to above at once. Therefor I am looking an efficient way to improve. I hope these will clear out some confusion. If you need the whole procedure, I can forward to you. ( it will be too big to post here and too messy ). Thanks you again.

C Chang Received on Wed Feb 19 2003 - 22:44:40 CST