hi all

trying to figure out how to go thru 5 separate columns and find the
max value for each row from each field
thru SQL.


i don't see that informix has implemented the MAX scalar function as
it is done in DB2

any suggestions?



thanks
tom

No certain what you are looking for. Is it that you want to know the
greatest value for each row of the five columns and what that value is?

select keycols,
case when col1 >= col2 and col1 >= col3 and col1 >= col4 and col1
col2 >= col5 then col2
when col3 >= col1 and col3 >= col2 and col3 >= col4 and
col3 >= col5 then col3
when col4 >= col1 and col4 >= col2 and col4 >= col3 and
col4 >= col5 then col4
when col5 >= col1 and col5 >= col2 and col5 >= col3 and
col5 >= col4 then col5
end case,
othercols....

Or just write a small SPL or C function and pass in the five values and
return the greatest:

create function greatest_of_five( int col1, int col2, int col3, int col4,
int col5 ) returning int;
if col1 >= col2 and col1 >= col3 and col1 >= col4 and col1 >= col5
then return col1
elif col2 >= col1 and col2 >= col3 and col2 >= col4 and col2 >= col5
then return col2
elif col3 >= col1 and col3 >= col2 and col3 >= col4 and col3 >= col5
then return col3
elif col4 >= col1 and col4 >= col2 and col4 >= col3 and col4 >= col5
then return col4
elif col5 >= col1 and col5 >= col2 and col5 >= col3 and col5 >= col4
then return col5
end if
end function;

select keycols, greatest_of_five( col1, col2, col3, col4, col5 ), othercols
....
....

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.



On Wed, Jan 4, 2012 at 3:41 PM, Tom Lehr <tomcaml (AT) gmail (DOT) com> wrote:

This was sent only to Art by mistake (sorry for the duplicate Art):

Informix allows you to do weird stuff also... getting the max from
customer's table columns zipcode and customer_num (it will always be the
zipcode value):

CREATE PROCEDURE get_max(i SET) RETURNING INTEGER;
DEFINE mymax,curr_val INTEGER;

LET mymax = NULL;
FOREACH
SELECT t.*
INTO curr_val
FROM TABLE(i) t

IF (curr_val > mymax OR mymax IS NULL) THEN
LET mymax=curr_val;
END IF;

END FOREACH

RETURN mymax;
END PROCEDURE;

SELECT
get_max(SET{zipcode,customer_num}), customer_num
FROM customer;

May need some testing... but looks geeky
Ideally the SELECT inside the procedure would be a simple SELECT MAX() but
I didn't figure out how to "name" the pseudo column...

Regards.

On Wed, Jan 4, 2012 at 9:02 PM, Art Kagel <art.kagel (AT) gmail (DOT) com> wrote:


--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Over complicated things:

select
(
select MAX(t1.col1)
from table(
set{t0.zipcode::integer, t0.customer_num::integer}
) t1(col1)
) max_zip_cust_num,
zipcode, customer_num
from customer t0;


max_zip_cust_num zipcode customer_num

94086 94086 101
94117 94117 102
94303 94303 103
94026 94026 104
94022 94022 105
94063 94063 106
94304 94304 107
94063 94063 108
94086 94086 109
94062 94062 110
94085 94085 111
94022 94022 112
94025 94025 113
94062 94062 114
94025 94025 115
94040 94040 116
94063 94063 117
94609 94609 118
8002 08002 119
85016 85016 120
19898 19898 121
8540 08540 122
32256 32256 123
74006 74006 124
2135 02135 125
80219 80219 126
60406 60406 127
85008 85008 128


The casts assure that we're creating a set of integers...
So I think you can easily adapt this to your query?

Regards.

On Thu, Jan 5, 2012 at 11:44 PM, Fernando Nunes <domusonline (AT) gmail (DOT) com>wrote:



--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...