Home » SQL & PL/SQL » SQL & PL/SQL » SQL to retrieve lines based on a static value
SQL to retrieve lines based on a static value [message #682502] Mon, 26 October 2020 16:22 Go to next message
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 #682503 is a reply to message #682502] Mon, 26 October 2020 16:42 Go to previous messageGo to next message
BlackSwan
Messages: 26756
Registered: January 2009
Location: SoCal
Senior Member
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Re: SQL to retrieve lines based on a static value [message #682506 is a reply to message #682503] Tue, 27 October 2020 00:38 Go to previous messageGo to next message
891ashok@gmail.com
Messages: 5
Registered: October 2020
Junior Member
Sorry, I just changed the sample data format.

I am new to this group.
I have a requirement where I am unable to nail down the logic.

Here is a table orders. I need to pull out the lines with a logic where the total (sum)quantity of lines is based on a static value.

Orders : 

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 as long as the sum of the quantity matches the static value.


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 Go to previous messageGo to next message
John Watson
Messages: 8426
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 #682508 is a reply to message #682507] Tue, 27 October 2020 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 67545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Re: SQL to retrieve lines based on a static value [message #682510 is a reply to message #682508] Tue, 27 October 2020 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Some questions:
  • if you pass 31 is 5 alone a solution?
  • if you pass 32 is 1,3,4 a solution?
  • Do you search for solutions with 2 rows and exactly 2 rows or any number of rows?
Re: SQL to retrieve lines based on a static value [message #682511 is a reply to message #682507] Tue, 27 October 2020 03:57 Go to previous messageGo to next message
891ashok@gmail.com
Messages: 5
Registered: October 2020
Junior Member
Here are the Create and Insert statements for my issue.

create table orders (line_id number, quantity number);

insert into orders values(1,22);
insert into orders values(2,23);
insert into orders values(3,2);
insert into orders values(4,8);
insert into orders values(5,31);
insert into orders values(6,8);
insert into orders values(7,7);

select * from orders;

line_id Quantity
------- ---------
1       22
2       23
3       2
4       8
5       31
6       8
7       7
Re: SQL to retrieve lines based on a static value [message #682512 is a reply to message #682511] Tue, 27 October 2020 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And the answer of my questions (2 posts)?

Re: SQL to retrieve lines based on a static value [message #682516 is a reply to message #682510] Tue, 27 October 2020 05:38 Go to previous messageGo to next message
891ashok@gmail.com
Messages: 5
Registered: October 2020
Junior Member

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.
Re: SQL to retrieve lines based on a static value [message #682517 is a reply to message #682516] Tue, 27 October 2020 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And Oracle version?

Re: SQL to retrieve lines based on a static value [message #682522 is a reply to message #682517] Tue, 27 October 2020 11:14 Go to previous messageGo to next message
891ashok@gmail.com
Messages: 5
Registered: October 2020
Junior Member
12.1.0.2.0
Re: SQL to retrieve lines based on a static value [message #682524 is a reply to message #682522] Tue, 27 October 2020 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You want one solution or all solutions?

Re: SQL to retrieve lines based on a static value [message #682536 is a reply to message #682516] Tue, 27 October 2020 18:43 Go to previous message
Solomon Yakobson
Messages: 3007
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.
Previous Topic: temp table not returning data in WITH
Next Topic: ORA-1403 when trying to select after Insert
Goto Forum:
  


Current Time: Sat Nov 28 23:09:32 CST 2020