I'm still new to TeraData, so most of my issues and inquiries are probably simple. I've done a few searches, and can't seem to find much...
If I have 5 columns, itm_cd1 thru itm_cd5, is there an easy way to select the highest value of the 5?
CASE WHEN (itm_cd1>=itm_cd2 thru itm_cd5 THEN itm_cd1 ELSE (CASE WHEN (itm_cd2>=itm_cd1,itm_cd thru itm_cd 5 THEN itm_cd2 ELSE...etc.
I haven't actually tried that--but is there a shorter/simpler way?
One way to do this is:
select max(itm_cd1) from table
select max(itm_cd2) from table
select max(itm_cd3) from table
select max(itm_cd4) from table
select max(itm_cd5) from table
) main (vals);
There is no elegant way below 14 and you need to take care of the data types but in case you are on 13.10 a SQL udf can limit the uglyness of the code.
REPLACE FUNCTION yourdb.colmax (i1 decimal(18,8), i2 \
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
INLINE TYPE 1
Case when i1 > i2 then i1 else i2 end;
Where you can define a second SQL udf to deal with the 5 columns.
In TD 14 the function Greatest is introduced.