Link to home
Start Free TrialLog in
Avatar of Barbara69
Barbara69Flag for United States of America

asked on

How to search for missing numbers in a table against a range of numbers?

I have a table with customer ids and I have a range of numbers I need to search against the table ids and when a number in the range isn't in the table output a message using an explicit cursor in a procedure.. The table ids are 1, 1, 1, 4, 4, 5, 5, 7, 8, 8 and the range of numbers are 1-8  How do I search through the table and range to get the missing numbers from the table, which are 2, 3, and 6 and print out the message in numerical order? I've been able to get 1, 2, 4, 5, 7, 6, 8. Please see the attached.
Attempt.txt
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

You can do this with straight SQL.  No need to procedural code
drop table tab1 purge;
create table tab1(cust_id number);

insert into tab1 values(1);
insert into tab1 values(1);
insert into tab1 values(1);
insert into tab1 values(4);
insert into tab1 values(4);
insert into tab1 values(5);
insert into tab1 values(5);
insert into tab1 values(7);
insert into tab1 values(8);
insert into tab1 values(8);
commit;


select
	case
		when counter=cust_id then counter || ' has at least one order'
		when cust_id is null then counter || ' no purchase order'
	end
from (
select counter, cust_id from
(select distinct cust_id from tab1)
right outer join
(select level counter from dual connect by level <=8) on counter=cust_id
)
order by counter
/

Open in new window


If you still want code, just copy/paste the select above and it is a simple cursor loop.  No need for the if/then and other loops.
Not sure what you really need.  What you have attached doesn't match with what you are asking for.  Based on what you are asking for, which is find the missing numbers in the range, then this will certainly work:
SELECT           t.lvl missing_id 
FROM             purchase_order 
right outer join 
                 ( 
                        SELECT LEVEL lvl 
                        FROM   dual 
                               CONNECT BY LEVEL < 9) t 
ON               purchase_order.cust_id = t.lvl 
WHERE            purchase_order.cust_id IS NULL;ORDER BY cust_id;

Open in new window

If you are trying to find more information than that, we'll need some help.  You really don't need a procedure to find what you are looking for.
Apparently slightwv and I were typing at the same time...

Now, if you want to do what is in your procedure where a range is being specified, then you can do something like this.  This is done with SQL*Plus, so not all other tools will set up bind variables the same way.

First set up the variables:
variable lv_low_custid number;
variable lv_high_custid number;
begin
  :lv_low_custid := 90001;
  :lv_high_custid := 90008;
end;
/

Open in new window

Then the query would look like this:
SELECT t.lvl missing_id 
FROM   purchase_order 
       right outer join (SELECT LEVEL + :lv_low_custid - 1 lvl 
                         FROM   dual 
                         CONNECT BY LEVEL <= :lv_high_custid - :lv_low_custid + 
                                             1) t 
                     ON purchase_order.cust_id = t.lvl 
WHERE  purchase_order.cust_id IS NULL; 

Open in new window

If you want the list to show ids with orders as well, then you need to make it similar to what slightwv posted, the only changes should be in the select list.

Showing the code with bind variables rather than substitution variables because the bind variable version is what should be put into code.
>>Apparently slightwv and I were typing at the same time...

Yes, we have no lives....  ;)
Avatar of Barbara69

ASKER

slightwv,
How do I get your code to work with cust_ids that have 9000 as the first 4 numbers of the ids?
To modify, slightwv's query to use a hard coded value, just put the math into the subquery that generates the list of numbers.
SELECT CASE 
         WHEN counter = cust_id THEN counter 
                                     || ' has at least one order' 
         WHEN cust_id IS NULL THEN counter 
                                   || ' no purchase order' 
       END 
FROM   (SELECT counter, 
               cust_id 
        FROM   (SELECT DISTINCT cust_id 
                FROM   tab1) 
               right outer join (SELECT LEVEL + 90000 counter 
                                 FROM   dual 
                                 CONNECT BY LEVEL <= 8) 
                             ON counter = cust_id) 
ORDER  BY counter ;

Open in new window

Based on the original procedure you posted, it looked to my like you wanted the dynamic range, so I didn't give the hardcoded answer.
Is there a way that the code I submitted initially can be tweaked to get the correct results? When I run my code, I get all the correct ids with purchase and without purchase orders except 90003 is missing from the list and the results are out of order.
What did you try?

I took your sample data, added 90000 to each id.  Then, I ran the query in the last post I made.  The results are:
CASEWHENCOUNTER=CUST_IDTHENCOUNTER||'HASATLEASTONEORDER'WHENCUS
---------------------------------------------------------------
90001 has at least one order
90002 no purchase order
90003 no purchase order
90004 has at least one order
90005 has at least one order
90006 no purchase order
90007 has at least one order
90008 has at least one order

8 rows selected.

Open in new window

That looks correct to me.
Hi johnsone,

I thought I had initially attached the file. Please see the attached.
Attempt.txt
What you attached is a procedure.  There is no sample data and expected results.

You do not need a procedure to do what you are asking.  It is easily done in a single query.

From what I can see, solutions have been provided that should work.  If they are not working, then we need sample data and expected results so that we can change what has been posted so that it will work.
How to run the code for different ranges (i.e. the first time the range is 1-8 (2, 3, 6 no PO), the second time the range is 3-7 (3, 6 no PO)?
You can either change the number being added to the level in post ID: 40744871, or use the method posted in ID: 40744179
johnsone, how do I get your code to work using sql developer.
johnsone, never mind, I copied to sql*plus and it works.
I know you say this can be done without a procedure, but I understand what I created and would like it if someone would tweak it to work the same as some of the other code that's been presented.
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial