Order By Result Difference in TD and DB2 Using Dense_Rank() Function with it

Database

Order By Result Difference in TD and DB2 Using Dense_Rank() Function with it

Hi All,

Order by Behavior is different in DB2 and Teradata 

I already tried adding Case specific which doesn’t work for my project related data .

Here is the main query modified (table-name etc for security reasons and its output Demo Data but similar to main one )

SELECT TESTCOL,NUMBERCOL+DENSERANK() OVER(
ORDER BY NUMBERCOL,(TESTCOL) ASC)*10000 AS NUMBERCOL
FROM TABLENAME1

OUTPUT:
TERADATA DB2
TESTCOL NUMBERCOL TESTCOL NUMBERCOL
CAAF 1190000.00 Canal Espelhado 700-Regra 1 1190000
CAAF 1190000.00 Canal Espelhado 700-Regra 2 1200000
CORPORAÇÃO ACIVS 1200000.00 Canal Espelhado 752-Regra 1 1210000
CORPORAÇÃO ACIVS 1200000.00 Canal Espelhado 752-Regra 2 1220000
CSARF 1210000.00 Carteiras Gerenciadas 1230000
Canal Espelhado 700-Regra 1 1220000.00 Cartões 1240000
Canal Espelhado 700-Regra 2 1230000.00 CAAF 1250000
Canal Espelhado 752-Regra 1 1240000.00 CAAF 1250000
Canal Espelhado 752-Regra 2 1250000.00 Chile 1260000

As you see in the above table the Dense Rank column is giving different result and hence affecting other related objects.

Please provide some solution so that Order by Data matches for DB2 and TD in the end with the above shown data .

Case Specific will work when the Characters don't have any Portuguese characters .

Regards

Jithin James

3 REPLIES
Enthusiast

Re: Order By Result Difference in TD and DB2 Using Dense_Rank() Function with it

I am not an expert in DB2. However, I think you can refer to this link and its explanation on order by and compare with that of DB2.

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1146_111A/ch01...

It can be due to the way they work differently. It is just my opinion.

Enthusiast

Re: Order By Result Difference in TD and DB2 Using Dense_Rank() Function with it

I had a similar situaton where the order by gives different results between Windows and Mainframe, and the issue was resolved by setting the collating sequence as below. You can try one of the two below. Hope this helps.

.SET SESSION COLLATION EBCDIC ;

.SET SESSION COLLATION ASCII ;

Teradata Employee

Re: Order By Result Difference in TD and DB2 Using Dense_Rank() Function with it

It is best to attempt to match the collation in use by DB2. This could be part of a DB2 locale specification. In Teradata, collation can be customized with the Multinational collation, or if the collation matchs the binary order of the client character set, then CHARSET_COLL can be used. See:

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/International_Character_Set/B035_...

-Dave