Fetching maximum value from a VarChar Field

Database
Enthusiast

Fetching maximum value from a VarChar Field

Hi All,

We are trying to pull the maximum value from a varchar column which is case specific.

TD output is compared to DB2 ouput and both differs. We need to match with DB2 output and we are going with the rank function.

create volatile table tab1(

name varchar(50) casespecific)

on commit preserve rows;

ins tab1('Teradata Forum');

ins tab1('Teradata FORUM');

sel max(name) from tab1;

Result --Teradata Forum

where the expected result is Teradata FORUM.

Wanted to understand if it is a bug or are we doing something wrong.

--Kathir

3 REPLIES
Junior Contributor

Re: Fetching maximum value from a VarChar Field

Hi Kathir,

this is based on the sort order, see:

SET SESSION COLLATION ASCII; 
SEL MAX(NAME), MIN(NAME) FROM tab1;

Teradata Forum Teradata FORUM

SET SESSION COLLATION ebcdic;
SEL MAX(NAME), MIN(NAME) FROM tab1;

Teradata FORUM Teradata Forum

SET SESSION COLLATION multinational;
SEL MAX(NAME), MIN(NAME) FROM tab1;

Teradata FORUM Teradata Forum
Enthusiast

Re: Fetching maximum value from a VarChar Field

many thanks dieter...

We are facing this issue while comparing the reports when executed from TD and DB2. 

But changing the collation setting will that affect the data set pulled.

--Kathir.

Junior Contributor

Re: Fetching maximum value from a VarChar Field

Hi Kathir,

of course the result might change for different collations, you must check which collation is used in DB2 and if there's an equivalent in Teradata...