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:

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

BUT I currently know that the current min(column_valu e) 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_valu e + 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_valu e)?

or

simply insert MAX(column_valu e) + 1?

[E11esar]

Hi there.

No the range has to be less than the current min(column_valu e) 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_valu e +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]

Hi there.

No the range has to be less than the current min(column_valu e) 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_valu e +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]

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:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NO CYCLE