hi ppl...

i am entering dat in an access front end connect to an oracle database.
i am using max (number) + 1 increasing my id...

problem is ... if during testing i enter a number like 9999999 in the
database.
Even whn record is deleted i get the next value as 9999999 + 1.
how do retrieve the maximum value that is in the table.

thx in advance...

Reiro wrote:
> hi ppl...
>
> i am entering dat in an access front end connect to an oracle database.
> i am using max (number) + 1 increasing my id...
>
> problem is ... if during testing i enter a number like 9999999 in the
> database.
> Even whn record is deleted i get the next value as 9999999 + 1.
> how do retrieve the maximum value that is in the table.
>
> thx in advance...

By not allowing anyone to enter a number in this column.
Assuming the sql statement is correct, as you entered the maximum value
of 9999999 in this column, you will retrieve this number as the maximum
value.
Nothing can be done about that: what you see is what you get.

--
Sybrand Bakker
Senior Oracle DBA

so even tho the record 9999999 has been deleted and the max number i
can see in the database in 200000. i wont be able to retrieve that
value.... which in fact is the maximum number existing in the database.

Reiro wrote:
> so even tho the record 9999999 has been deleted and the max number i
> can see in the database in 200000. i wont be able to retrieve that
> value.... which in fact is the maximum number existing in the database.


You never posted the exact sql statement, did you?
But if the record was really deleted, *and* you *committed* the
transaction,
either your statement is wrong, or your understanding of Oracle, or
there is a serious bug in the yet still unknown version of your
database.

--
Sybrand Bakker
Senior Oracle DBA

im using oracle 9i and my query for increase the id is
nz(max(asset_id),0) + 1 its incorpated into my insert statement.

Reiro wrote:
> hi ppl...
>
> i am entering dat in an access front end connect to an oracle database.
> i am using max (number) + 1 increasing my id...
>
> problem is ... if during testing i enter a number like 9999999 in the
> database.
> Even whn record is deleted i get the next value as 9999999 + 1.
> how do retrieve the maximum value that is in the table.
>
> thx in advance...
>

To retrieve the max number in a column of a table, use a query similar
to the following:

SELECT MAX(column_name) FROM table_name;

However, adding one to this max number smells suspiciously like one
should be using an Oracle Sequence as it will autoincrement an interval
based on the last value used. Please read the Oracle docs
(http://tahiti.oracle.com) for more information.

HTH,
Brian

--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Reiro wrote:
> im using oracle 9i and my query for increase the id is
> nz(max(asset_id),0) + 1 its incorpated into my insert statement.
>

What is the NZ function? Any reason why you can't code a trigger to do
this for you on INSERT? Any reason why you can't use a sequence to
determine the number?

Cheers,
Brian

--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Reiro wrote:
> hi ppl...
>
> i am entering dat in an access front end connect to an oracle database.
> i am using max (number) + 1 increasing my id...
>
> problem is ... if during testing i enter a number like 9999999 in the
> database.
> Even whn record is deleted i get the next value as 9999999 + 1.
> how do retrieve the maximum value that is in the table.
>
> thx in advance...

If this is connecting to Oracle you should create a sequence and not be
doing manual numbering which will not scale.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

i having trouble with the sequence.... in my access query i have the
foll:


insert into asset(asset_id,parent_asset_id,content_title,asset _level)
values (asset_seq,parfld1,parfld2,parfld3)

if i try my parameter fields it works fine.... please let me know how
i can call the sequence in access through the query above..

thx alot
SORRY (im a beginner of all beginners)

On 24 Aug 2006 01:32:46 -0700, "Reiro" <ReiroGP@gmail.com> wrote:

>
>i having trouble with the sequence.... in my access query i have the
>foll:
>
>
>insert into asset(asset_id,parent_asset_id,content_title,asset _level)
>values (asset_seq,parfld1,parfld2,parfld3)
>
>if i try my parameter fields it works fine.... please let me know how
>i can call the sequence in access through the query above..
>
>thx alot
>SORRY (im a beginner of all beginners)


You can do that probably by using a passthrough query.
A workaround I used on interactive forms was to create a PL/SQL
function to run <sequence>.nextval, call that in a pass through query,
and assign the retrieved value to the ID column on my form.

--
Sybrand Bakker, Senior Oracle DBA