Re: SQL Question....

From: Chuck Hamilton <chuckh_at_dvol.com>
Date: 1998/01/13
Message-ID: <34bc9584.11676954@news.dvol.com>#1/1

On Fri, 09 Jan 1998 17:05:21 -0800, Ron Many <rmany_at_pacbell.net> wrote:

>Can someone suggest an efficient way to select rows with qty field up to
>a maximum number of qty?
>
>Here is what I need: I have a table T1 with a columns TYPE and QTY, I
>need to select all rows with TYPE=<type> but up to some maximum quantity
>(i.e. dynamically summing QTY).
>
>I'm using a cursor with a counter but my problem is that I need to do it
>simultaniously on several TYPE's and each to different maximums and
>every time different set of TYPE's. and it is very slow.
>
>any suggestions?
>
>Thank You
> Ron

Let me make sure I understand so I'll restate the problem. You want to select all rows for each specified type until you hit a cumulative sum of qty, which is different for each type?

You're probably best off doing this with PL/SQL. Create a table with the types and cumulative qty that you want to stop at and write a procedure that scans each type up to that qty.

Here's some _untested_ code that'll give you the idea of what I'm talking about.

create table query_params (

	type	varchar2(10),
	maxqty	number(9,0)

);
[populate table here]

create table results as select * from t1 where rownum < 1;

declare

	cumqty	pls_integer;
	cursor c_query_params is select * from query_params;
	cursor c_t1 (a1 varchar2) is select * from t1
		where type = a1;
begin
	for r1 in c_query_params loop
		cumqty := 0;
		for r2 in c_t1(r1.type) loop
			if cumqty + r2.qty <= r1.maxqty then
				cumqty := cumqty + r2.qty;
				insert into results
					values (r2.type, r2.qty);
			end if;
		end loop;
	end loop;

end;
/

select * from results;

You may need to change the insert statement if there are more columns in t1 you want to populate.

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself!

Received on Tue Jan 13 1998 - 00:00:00 CST