Getting a MAX() with an upper boundary

E11esar

Hi there.

I am looking to insert a value into a column but want to ensure the value remains in a range less than the current max and min values for the specific column.

I want to run a command such as:

Expand|Select|Wrap|Line Numbers

select max(column_value + 1) from tableName where column_value < 100;

BUT I currently know that the current min(column_value) is 200.

At the moment my select is returning nothing as there is no value less than 100 but is there a way to create this max(column_value + 1) amount by applying some form of control please?

Thank you.

M :o)

amitpatel66

You can always create such constraints by creating a TRIGGER on the table and checking that it is in specific range before insert.

And if you are looking at inserting a number 1 + MAX(col_value) then why not use a sequence instead of adding 1 to max value every time you do an insert?

E11esar

Hi there and thank you for that.

Regarding the trigger approach, how would do the insert part though as either way I need a vale that is greater than the current lowest value (1 being the first ever entry)up to the maximum?

In the real-world scenario this will be around 10,000,000.

Thank you.

M :)

amitpatel66

What would be the range you would like to check?

Is it like NOT between 1 and MAX(column_value)?

or

simply insert MAX(column_value) + 1?

E11esar

Hi there.

No the range has to be less than the current min(column_value) and a lot more besides.

In the current column are values that represent a unique reference number and for items entered into the column, there are to be entries which are pending approval. When these items are approved then they will be added to the column with the usual MAX(column_value +1) type of insert.

What I need is to insert values in a lower range which are definitely not within the approved column_value range, hence using a value between 1 and 10,000,000 is suitable.

Mark :)

amitpatel66

Quote:

Hi there.

No the range has to be less than the current min(column_value) and a lot more besides.

In the current column are values that represent a unique reference number and for items entered into the column, there are to be entries which are pending approval. When these items are approved then they will be added to the column with the usual MAX(column_value +1) type of insert.

What I need is to insert values in a lower range which are definitely not within the approved column_value range, hence using a value between 1 and 10,000,000 is suitable.

Mark :)

You want the value to be inserted lesser than the range 1..10,000,000 ?

E11esar

Quote:

Originally Posted by amitpatel66

You want the value to be inserted lesser than the range 1..10,000,000 ?

Hi there.

Yes that is right.

Thank you.

M :o)

amitpatel66

So in real time application, what is the value for MIN(column_name) ? Is it 1 or 200 as you said in your first post?

What you can do here is just create a sequence starting with 1 and maximum till 199 in case if your column has 200 as MIN value and use that sequence while inserting new record in to a table

In case if the MIN(column_name) is 1 then is it fine to insert negative values in the table? If yes then you can do so using a sequence again.

Sample Code:

Expand|Select|Wrap|Line Numbers

  
CREATE SEQUENCE supplier_seq

    MINVALUE 1

    MAXVALUE 999999999999999999999999999

    START WITH 1

    INCREMENT BY 1

    NOCACHE

    NO CYCLE

Getting a MAX() with an upper boundary

re: Getting a MAX() with an upper boundary

re: Getting a MAX() with an upper boundary

re: Getting a MAX() with an upper boundary

re: Getting a MAX() with an upper boundary

re: Getting a MAX() with an upper boundary

re: Getting a MAX() with an upper boundary

re: Getting a MAX() with an upper boundary

Similar Oracle Database bytes

/bytes/development

/bytes/it

/bytes/about