SQL to retrieve lines based on a static value [message #682502] |
Mon, 26 October 2020 16:22 |
|
891ashok@gmail.com
Messages: 5 Registered: October 2020
|
Junior Member |
|
|
Hi Team,
I am new to this group.
I have a requirement where I am unable to nail down the logic.
Here is a table. I need to pull out the lines with a logic where the total (sum)quantity of lines is based on a static value.
line id Quantity
------- ---------
1 22
2 23
3 2
4 8
5 31
6 8
7 7
If I pass the static value as 30
The SQL need to pull
Line id 1,4 or 1,6 or 2,7
Any combination is sufficient.
Your help is much appreciated.
Thanks,
Ashok
|
|
|
|
|
Re: SQL to retrieve lines based on a static value [message #682507 is a reply to message #682506] |
Tue, 27 October 2020 01:44 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You omitted to provide the CREATE TABLE and INSERT statements needed to set up your problem. However, for example,orclz> select a.ename,a.sal from emp a join emp b on (a.sal + b.sal = 2400);
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
ADAMS 1100
MILLER 1300
orclz>
|
|
|
|
|
|
|
|
|
|
|
Re: SQL to retrieve lines based on a static value [message #682536 is a reply to message #682516] |
Tue, 27 October 2020 18:43 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
891ashok@gmail.com wrote on Tue, 27 October 2020 06:38
if you pass 31 is 5 alone a solution? Yes
if you pass 32 is 1,3,4 a solution? Yes
Do you search for solutions with 2 rows and exactly 2 rows or any number of rows? Number of rows doesn't matter.
set verify off
define stop_quantity=30
with r(
line_id,
quantity,
running_quantity,
line_id_list,
stop_flag
) as (
select line_id,
quantity,
quantity running_quantity,
to_char(line_id) line_id_list,
case max(quantity) over() when &stop_quantity then 1 else 0 end stop_flag
from orders
where quantity <= &stop_quantity
union all
select o.line_id,
o.quantity,
r.running_quantity + o.quantity running_quantity,
r.line_id_list || ',' || o.line_id line_id_list,
case max(r.running_quantity + o.quantity) over() when &stop_quantity then 1 else 0 end stop_flag
from r,
orders o
where o.line_id > r.line_id
and r.running_quantity + o.quantity <= &stop_quantity
and r.stop_flag = 0
)
select line_id_list
from r
where running_quantity = &stop_quantity
and rownum = 1
/
LINE_ID_LIST
--------------------
1,4
SQL> define stop_quantity=17
SQL> /
LINE_ID_LIST
--------------------
3,4,7
SQL> define stop_quantity=48
SQL> /
LINE_ID_LIST
--------------------
3,4,5,7
SQL>
SY.
|
|
|