Home » SQL & PL/SQL » SQL & PL/SQL » Return a random value from a list of numbers minus a specified range
Return a random value from a list of numbers minus a specified range [message #200291] Mon, 30 October 2006 04:17 Go to next message
Sman
Messages: 9
Registered: August 2005
Junior Member
Hiya,

I have a scenario for which i can really appreciate a bit of help

I have a range of numbers (1 to 500). Also I have another list of numbers stored in a varray(The number can be numbered or random).

The problem is to pick a number from list A (1..500) which is not present in list B (present in varray) randomly.

I can create a solution using two FOR loops and counters but I was wondering if there is an easier and more classier solution available.
Re: Return a random value from a list of numbers minus a specified range [message #200348 is a reply to message #200291] Mon, 30 October 2006 07:47 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
If you make the numbers the index of a PL/SQL table, then you can simply check whether they exist using the .exists operator e.g.

declare
   type t_array is table of varchar2(1) index by binary_integer;
   type t_lookup is varray(5) of number;
   v_array t_array;
   v_lookup t_lookup := t_lookup(1,3,5);
begin
  v_array(1) := 'x';
  v_array(2) := 'x';
  v_array(3) := 'x';
  v_array(8) := 'x';
  v_array(10) := 'x';
  for i in v_lookup.first..v_lookup.last loop
     if v_array.exists(v_lookup(i)) then
        dbms_output.put_line('Found '||v_lookup(i));
     else
        dbms_Output.put_line('Did not find '||v_lookup(i));
     end if;
  end loop;
end;

[Updated on: Mon, 30 October 2006 07:48]

Report message to a moderator

Previous Topic: Need help with approach to SQL SELECT
Next Topic: Date query
Goto Forum:
  


Current Time: Mon Dec 09 19:36:54 CST 2024