Query for non-existent codes 2

[Nunina]

Hi Guys!

I have a table that contains all the vendor codes and descriptions. For example:

Cod_vendor Vendor_desc
--------------------------------
108553 Vendor1
108556 Vendor2
108559 Vendor3
108561 Vendor4
108563 Vendor5

What I want is to create a query that allows me to know that vendor 108554, 108555, 108557, 108558, 108560 and 108562 does not exists in the table.

Is that possible? If so, can you please show me?

As usual, thanks again for all your help.

Nunina


Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[nagornyi]

How to know which vendors should exist in the table?

 

[Turkbear]

Hi,
It is a little difficult to ask a database to tell what values it does not have.

If there should be a cod_Vendor for every number from the lowest in the data to the highest, you could probably write some Pl/Sql code to loop through a cursor and note which numbers are not there..

 

[Nunina]

Well, the numbers go from 100000 to 109999.

What I am going to do is create a temp table with all the numbers from 10000 to 109999. Then, do a join and then see if that shows me what I need.

What do you think?

Thanks!

Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[SantaMufasa]

Issys,

There are probably many ways to solve your need, but here is some code that works:

col cnt heading "Vendor|Code" format 9999999
col name heading "Vendor|Name" format a20
select cnt,nvl(vendor_desc,'No Vendor exists') name
	from	vendor
		,(select (rownum-1)+min_cd cnt, max_cd from all_objects
			,(select min(cod_vendor) min_cd, max(cod_vendor) max_cd from vendor)) a
where cnt = cod_vendor(+)
  and cnt <= max_cd
order by cnt
/

  Vendor Vendor
    Code Name
-------- --------------------
  108553 Vendor1
  108554 No Vendor exists
  108555 No Vendor exists
  108556 Vendor2
  108557 No Vendor exists
  108558 No Vendor exists
  108559 Vendor3
  108560 No Vendor exists
  108561 Vendor4
  108562 No Vendor exists
  108563 Vendor5

Let us know if this is satisfactory,

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:15 (23Jun04) UTC (aka "GMT" and "Zulu"), 15:15 (23Jun04) Mountain Time)

 

[Nunina]

Thanks a lot, Mufasa!!!!

It worked beautifully!!! I'm giving you a star!!!

The only thing is that it begins at number 1, when I actually need it to begin at number 100000...

Thanks a lot! Here is your star!

Nunina

Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[SantaMufasa]

Nunina,

Thanks for the star. The code I posted should begin at whatever is the smallest value in "cod_vendor". If your smallest value is 100000, but it is starting at 1, then something is wrong.

Please advise,

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:30 (23Jun04) UTC (aka "GMT" and "Zulu"), 15:30 (23Jun04) Mountain Time)

 

[nagornyi]

To show those non-existing vendors, may also use

select v.cod_vendor
from vendors v,
(select rownum+99999 cod_vendor from all_objects where rownum<110000) av
where av.cod_vendor = v.cod_vendor(+)
and v.cod_vendor is null

Instead of all_objects any table having at least 10000 records can be used.

 

[SantaMufasa]

Nagornyi,

You code is very, very close; your SELECT should read:

select [b]a[/b]v.cod_vendor...

Without the correction, no numbers print out since they are all NULL.

Mufasa
(aka Dave of Sandy, Utah, USA @ 05:28 (24Jun04) UTC (aka "GMT" and "Zulu"), 22:28 (23Jun04) Mountain Time)

 

[nagornyi]

Thanks SantaMufasa, you are right of course. We need those non-existing vendors. Sure.

 

[sem]

As we already know queries against all_objects to get successive values are quite inefficient. Besides this view may not contain enough values (100K objects is quite big number).
In 9i such values may be easily generated by pipelined function, but for 8i I suppose to use pl/sql. Another idea that gets not the list of values, but rather intervals is

select Cod_vendor+1, next_Cod_vendor-1  from(
select Cod_vendor, 
lead(Cod_vendor,1) over (order by Cod_vendor) as next_Cod_vendor
from Vendors)
where Cod_vendor <> next_Cod_vendor-1

This is not complete solution, because you should add range condition and process extreme values

Regards, Dima

http://seminihin.narod.ru

 

[Nunina]

Nagornyi and SantaMufasa:

Thanks guys for helping me out. Both your posts were very helpful, although the first one Mufasa sent me had the problem that it began at 1 since the list actually begins at 0 and then jumps to 100000.

Once I used Nagornyi's post along with Mufasa's correction, it worked perfectly! So I give you both a star.

Sem, the table does not contain 100k records. It contains only 10k.

Thanks again guys!



Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico