CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Ken Chan
Ken Chan

Reputation: 90507

Dynamically get the maximum and minimum allowable value for a number column?

Suppose a column called money, which is NOT NULL and its type is NUMBER(x,y) .There is no constraint on this column.

I want to sort this money column according to this sequence , +ve > -ve > 0 , so my plan is to decode the 0 value to the minimum allowable value that the money column can hold in the order by clause, like select * from tableXXX order by decode(money, 0 , allowableMnimumValueForMoneyColumn , money) desc . I just wonder if it is possible to get the minimum allowable value for the money column dynamically.

How can I get the maximum and minimum allowable value for a column ? Does oracle has the implicit variables to do it?

Upvotes: 1

Views: 493

Answers (3)

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

You don't have to know the minimum value. You can treat NULL as the minimum value:

... ORDER BY decode(money, 0, NULL, money) NULLS LAST

Upvotes: 1

René Nyffenegger
René Nyffenegger

Reputation: 40543

It seems that you want the records whose value for money = 0 appear last.

If this is the case you would go by such an order clause:

order by 
case when money = 0 then 0
                    else 1 
end desc,
money desc

With a working example, that would be

create table tq84_order_by (
  txt   varchar2(10),
  money number not null
);

insert into tq84_order_by values ('aaa', 0);
insert into tq84_order_by values ('bbb', 2);
insert into tq84_order_by values ('ccc',-3);
insert into tq84_order_by values ('ddd', 4);
insert into tq84_order_by values ('eee', 1);

select * from tq84_order_by
order by 
case when money = 0 then 0
                    else 1 
                    end desc,
                    money desc;

resulting in

TXT             MONEY
---------- ----------
ddd                 4
bbb                 2
eee                 1
ccc                -3
aaa                 0

Upvotes: 1

WW.
WW.

Reputation: 24311

You need to create a check constraint on the column:

CREATE TABLE TEST (MONEY NUMBER(14,2) NOT NULL)
/

ALTER TABLE TEST ADD
  CONSTRAINT MONEY_VALID CHECK (( MONEY > 100 AND MONEY < 5000))
/

-- This fails
INSERT INTO TEST VALUES (20);

-- This works
INSERT INTO TEST VALUES (110);

Upvotes: 0

Related Questions