OraFAQ Forum: Server Administration » problem in finding least value

Home » RDBMS Server » Server Administration » problem in finding least value

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem in finding least value [message #53262]

Mon, 09 September 2002 23:09

Victoria
Messages: 152
Registered: July 2002

Senior Member

Hi,
i have 100 integer columns which can contain null values randomly.
Some of those 100 columns will contain valid integer values.
I want to find out the least among them.
So i used,
least(nvl(col1,0),nvl(col2,0).....nvl(col100,0));

now the problem is it is returning 0 as least value as some of the columns are containing nulls.
but i need to overcome this and i need only the least among the valid value columns.

for ex.
col1 = 1.1, col2=3.5, col3=2.3, col4=0.8, col5=null, col6=0.5, col7 to col100 = null;
here i need only 0.5.

I dont have maximum limit of that number so i cant use like nvl(col1,maximum value)....so that it will be bypassed in finding least value...

But i need to use this select statement as single line...i mean i have to use like
select
least(.......) min_value,
some other fields,
from
table1
where
.....

can any one help me out???

Thanks and regards
Victoria

Report message to a moderator

Re: problem in finding least value [message #53279 is a reply to message #53262]

Tue, 10 September 2002 16:37

andrew again
Messages: 2577
Registered: March 2000

Senior Member

Here's one way, but not very elegant...

create table null_cols (pk number, col1 number, col2 number, col3 number, col4 number, col5 number, col6 number, col7 number)

insert into null_cols values(11, 1.1, 3.5, 2.3, 0.8, null, 0.5, null);

insert into null_cols values(12, -1, 0, 0, 1, 2, null, null);

create or replace view null_cols_v as (
select pk, col1 The_Val from null_cols
union all
select pk, col2 from null_cols
union all
select pk, col3 from null_cols
union all
select pk, col4 from null_cols
union all
select pk, col5 from null_cols
union all
select pk, col6 from null_cols
union all
select pk, col7 from null_cols);

select pk, min(The_Val) from null_cols_v group by pk;
11 .5
12 -1

Report message to a moderator

Re: problem in finding least value [message #53282 is a reply to message #53262]

Tue, 10 September 2002 21:18

Victoria
Messages: 152
Registered: July 2002

Senior Member

HI,
i have used 1000 in place of zero as that column is of number(7,4)...anyway thanks a lot for your valuable reply...
V

Report message to a moderator

Previous Topic:	RBO access by index, CBO does full scan
Next Topic:	Datafile deleted.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 12:12:29 CDT 2024