How to tell a cursor how to pull a specific record

[daddypost]

I have a cursor that pulls product_id and size and orders them by size asc. I'm passing a size and number(either 1 or 2) to the function. basically what I'm needing to do is return the size that is the next closest to the size I pass in. The issue becomes when I have more than 1 size available. For example, I pass a size 5, and I have products that are size 4, 4.5, 5.5, and 6. If a number 1 is also passed into the function then I just need the lowest size closest to the size passed(in this case a 4.5). If the number passed in is a 2, then I need the next closest size on THE OTHER SIDE of the item passed. For example, I would not want to pass a size 4 if I had passed 4.5 as in the example. I hope that makes sense. So If I passed a 5, and all I had was a 5.5 and a 6, when I pass the 1, it should pull 5.5, when I pass the 2, it should return null. If I passed a size 5 and had a 4.5, and a 6, then when I pass 1, i should return the 4.5, and 2 should return the 6. Here is an example of what I have so far.
function size_count(v_master_id number, v_size_no number,n)

return number is

v_no_of_records number := 0;
-- v_current_rec number := 0;

CURSOR next_size IS
select s.master_ID,s.size_no--,(v_size_no-s.size_no) size_difference
from
acntv.sku s
where s.master_id = v_master_id and
(s.size_no<=v_size_no+1 or s.size_no>=v_size_no-1) and
s.on_hand>0
order by s.size_no asc;

BEGIN

FOR rec_index IN next_size LOOP
v_no_of_records := v_no_of_records + 1;

END LOOP;
Thank you for any help you can give me.

 

[SantaMufasa]

To clarify, Daddy, [ul][li]when you pass in a "1" to the function, do you want the next smaller size or the next closest size, regardless of whether it is smaller or larger?[/li][li]when you pass in a "2", you want first, the size per the rule choice you made, above, then second, you want the closest size (if there is a size) that is on the opposite side from the first size choice, right?[/li][/ul]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[daddypost]

To point 1, if there is more than 1 size available, then the next smallest size. If there is only 1 size available, then doesn't matter if larger or smaller.

To point 2, if there is only one result from the cursor, then null, if more than 1 then it will return the next closest size on the opposite side of the first choice. It should only return 1 value(being the closest on the opposite side of what was returned the when a "1" was passed).

Thank you for your help.

 

[SantaMufasa]

Sorry, Daddy...another question. Must the "next closest size" be +/- one size?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[daddypost]

The cursor should be pulling anything that is within 1 size up or down of the passed parameter size. That could include a full size or a half size up or down. The next closest size could be the full size(if thats the only one available) but it could be a half size as well. If I passed a size 4 then I could get a 3.5 on one side and when I run the parameter with a "2" it could pull a 5. does that helP>

 

[Dagon]

So, if you passed 5 and the only thing available was a 3, would you return 3 or NULL ?

 

[LKBrwnDBA]

1) Load all sizes for selected master_id in an array

2) Do binary search on the array and choose either the next element (larger) or previous element (smaller) than the target size and according to your requirements.





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[daddypost]

Dagon,
I'm sorry, I just got back in the office. If a 5 was passed and the only thing was a 3, we would want to pass a Null value.
KKBrwnDBA,
That makes sense, in theory, however, putting that in practice is something I've never done, nor know where to begin

Thank you all again for your help.

 

[Dagon]

I should have made my question a bit clearer:

So, if you passed 5 and the only thing available was a 3, would you return 3 or NULL when the second parameter is set to 1 ?

 

[Dagon]

Third parameter, that is.

 

[daddypost]

It would still return null. The initial portion of the cursor should only provide items that are within +-1 of the passed size requested.

 

[Dagon]

I'm still a bit unclear on exactly what the rules are and also why you need to loop through a cursor in the original. But I'll take a stab at it with this.

CREATE TABLE sku (master_id number, size_no number);

insert into sku values (1, 2.5);
insert into sku values (1, 3.5);
insert into sku values (1, 4.5);
insert into sku values (1, 5.5);
insert into sku values (2, 4.5);
insert into sku values (3, 6.0);

select size_count(2, 4.0,2) from dual

create or replace function size_count(v_master_id number, v_size_no number,n number)
  return number is
  --
  v_result number;
  type t_curs is ref cursor;
  v_curs t_curs;
BEGIN
  --
  if n = 1 then
    open v_curs for 
	select size_no
    from 
	(select size_no, row_number() over 
         (partition by master_id order by 
             abs(size_no-v_size_no), 
             sign(size_no-v_size_no)) as rn
	 from sku
	where master_id = v_master_id
	and  size_no between v_size_no-1 and v_size_no+1)
	where rn=1;
  else
    open v_curs for 
	 select min(size_no)
       from sku
	  where master_id = v_master_id
	    and size_no > v_size_no;
  end if;
  --
  FETCH v_curs into v_result;
  close v_curs;
  return v_result;
end;
select size_count(1, 5.0,1) from dual

4.5

select size_count(1, 5.0,2) from dual

5.5