find Minimum in range thats not in Table?

Hi Folks,

any help appreciated on this problem:
I've got a Table with a comparable Datatype (inet on postgres).
The values in the table have a minimum and a maximum value.
Now, I've got to find the smallest value betweeen min. and max. that is NOT in the table.

Example:
Min=10, Max=20
Entries: 10, 11, 12, 14, 18
needed value: 13 (larger than Min., smallest value not in Table)
I dont have pure Numbers to deal with, so

I dont want to create an auxiliary table with all the possible values and do a SELECT ... WHERE NOT IN ... statement.

thanks in advance!could you give some examples of what "inet" values are?

kinda curious why you chose the oracle forum to post

http://dbforums.com/f81/ is the postresql forum

and what's wrong with an auxiliary table?

rudy|||Originally posted by r937
could you give some examples of what "inet" values are?

kinda curious why you chose the oracle forum to post

http://dbforums.com/f81/ is the postresql forum

and what's wrong with an auxiliary table?
rudy

Sorry, but my browser says 'SQL and PL/SQL' Forum; I'd say this is a SQL problem - not a postgres.

'inet' is an ip(v4)-address - this datatype is comparable but not incrementable.

I don't think it's necessary to create a table with some hundred continuous values that can be expressed by two borders; perhaps there is no other solution than an auxiliary table but that would be a pity.

thanks anyway, Z|||yes, you're right, there's a separate oracle forum, although this one should definitely have the "PL/SQL" taken off its name

if you want an sql solution and not a postgresql solution, the only ones i'm familiar with are:

-- NOT EXISTS
-- NOT IN
-- EXCEPT
-- OUTER JOIN with test for no match

each of these requires some way of specifying the set of things that aren't there

you don't actually have to have an auxiliary table, though

try joining the table to itself with a left outer join on a.inet = (b.inet - 1)

or something :rolleyes:

rudy|||Originally posted by r937
...
try joining the table to itself with a left outer join on a.inet = (b.inet - 1)

or something :rolleyes:

rudy

darn, you were right from the beginning: As the 'inet'-Type cant be incremented or decremented a pure SQL solution seems even more unapplicable now. I think I have to do some postgres-specific hack... :(

thanks,

zaphod|||yeah, but i didn't know i was right at the time!

meanwhile, i had a look at the postgresql docs, and found this (http://developer.postgresql.org/docs/pgsql/src/test/regress/expected/inet.out) page, which (a) freaks me out, but (b) offers encouragement that there might be custom functions available in postgresql for working with that datatype

rudy

Find column name and datatype of a given table

Hi,
How do we find the "column name" and "data type" of all the columns in a table. Assuming that I know the Table name or Table Object ID. I am using Microsoft SQL Server 2000.
Thanks
-SudhakarHi sudhakar_112

Have a peak at the INFORMATION_SCHEMA tables in BoL - terribly useful.

Specifically:
SELECT COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'

HTH|||Thank you..

using the following query lists only the columns that are acessible to the user. If the user does not have permission on a column it will not be displayed. But I want a query to display all column names irrespective of the user has any permissions to modify or select.

Thanks
-Sudhakar


Hi sudhakar_112

Have a peak at the INFORMATION_SCHEMA tables in BoL - terribly useful.

Specifically:
SELECT COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'

HTH|||You can construct a query on the syscolumns and sysobjects tables - these are not dependent on the user permissions.