All Forums Database
Jithin James 4 posts Joined 03/14
03 Feb 2015
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

 

Raja_KT 1246 posts Joined 07/09
03 Feb 2015

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.032.302.html
It can be due to the way they work differently. It is just my opinion.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Renji 17 posts Joined 08/05
03 Feb 2015

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 ;
 

Regards
John Abraham

david.craig 73 posts Joined 05/13
04 Feb 2015

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_1125_111K/ch07.09.01.html
 
-Dave

You must sign in to leave a comment.