Archives of the TeradataForum

Message Posted: Sun, 07 Aug 2005 @ 10:55:57 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Regarding usage Of MAX function

From:		Victor Sokovin

2. Consider doing an UPPER on the final value returned instead every single value, try: UPPER(MAX(<varchar-data>)). It will be faster to shift the data once instead of multiple times. The UPPER of the data and the lower of the data are both going to be the same MAX if you have only alpha characters.

The same is definitely true of numeric and I don't think there will be any difference with special characters. This is something you can experiment with using your data.

Well, there is likely to be a problem once you apply this logic beyond the standard ASCII character set (it seems to be the only standard set where it works). Even TD Latin is already a problem!

I can recall me posting on the subject a couple of times but I don't mind recycling my standard examples as I *love* this topic (could become my "fun assignments" in retirement - LOL!). I just hope I don't bore the list with them.

OK, let's take Latin as the session character set. Within this set we find such worthy characters as "Sharp S" and "C with cedilla". I know the names sound terribly sophisticated, decadent and perhaps unfamiliar but if they survive the posting process all the way, I am sure we will all recognize them as something very familiar from our trips to Germany and France: =DF (this the "Sharp S") and =E7 ("C with cedilla"). They are quite common letters in, respectively, German and French.

"Sharp S" is special in TD because its UPPER and LOWER are actually the same and are internally represented as 0xDF (I know I might be oversimplifying here but hey, it's a weekend).

"C with cedilla", on the contrary, does have distinct LOWER and UPPER variations: upper case is 0xC7, and lower case is 0xE7.

So, we seem to have the following case specific order: =C7 < =DF < =E7.

(For list members whose mail clients chop off non-ASCII stuff: upper case "C with cedilla" less than "Sharp S" less than lower case "C with cedilla").

As the UPPER operator leaves the value untouched for "Sharp S" but not for "C with cedilla" we do get different case specific orders for this pair. Is this pair really something exotic and special? No. There many more examples of this kind in "Latin", and if we go beyond Latin, don't even get me started.

Just one remark on MAX in this context for the OP: I have seen a few shops terribly hit by naive implementations of this kind. It is a bit of a nightmare, so please use it with great caution and do extensive research and testing.

Regards,

Victor


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference