Aggregate Operations with Case Specific data

Database

Aggregate Operations with Case Specific data

Hello All,

I have a report that is generating SUM for various code values. The data on which the aggregate is being run has a mix of upper and lower case data for the same value (e.g. 'AB', 'ab','Ab','aB'). The sum that is returned is correct, only thing is -  the query had been returning all uppercase values and now it is returning all lowercase values.

Is there any logic or algorithm that Teradata follows to what case will be returned by the aggregate operation?

I tried looking through the manuals for this but could find anything that illustrates this operation.

regards,

Mandar

3 REPLIES
Teradata Employee

Re: Aggregate Operations with Case Specific data

Please share your query for getting the SUM.

Ideally it should return seperate SUM values for Aa and AA.

Also, share the DDL of the table you are doing SUM on, are the columns defined as case-specific?

Senior Apprentice

Re: Aggregate Operations with Case Specific data

Hi Mandar,

the returned value is any of the existing values (more or less random), tere is no built-in rule like "prefer upper over lower case".

If you want consistent output you should add UPPER or LOWER.

Dieter

Re: Aggregate Operations with Case Specific data

Thank you Adeel and Deiter.

The query is a regular SUM query - SELECT RGN_CD, SUM(PD_AMT) FROM RGN_PYMNT GROUP BY 1. The column is defined as not casespecific.

This isnt really an issue but I am just curious if there is a specific behaviour to aggregating mixed case data.

And it seems as though there isnt, so any of the possible values could be returned by the aggregate operation.

Thanks again for your responses.

regards,

Mandar