Archives of the TeradataForum

Message Posted: Thu, 21 Jun 2012 @ 15:12:23 GMT

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

<-- Anonymously Posted: Thursday, June 21, 2012 07:12 -->

Hi,

I am using Teradata V2R5

My query is:

There is one column which I am fast exporting into a text file which has decimal as data-type. But when value in that column is NULL then I need to put some text e.g. 'NOT AVAILABLE' as a return value instead of NULL. This is converting data-type from decimal into varchar. The main problem is my column is in Italian, so the decimal will be shown as ',' instead of '.' And when I coalesce it to replace with the text if null, it converts the column into varchar and return '.' Instead of ',' which Is not accepted.

I wrote below query for that:

     Select
     (TRIM (Case when xamount IS NULL
     THEN 'NOT AVAILABLE'
     ELSE (Case when SUBSTRING (xamount
     From 0 For INDEX(xamount, '.')) = ''
     THEN '0' ELSE SUBSTRING (xamount
     From 0 For INDEX(xamount, '.') ) END)||','||
     SUBSTRING (xamount
     From INDEX(xamount, '.') + 1 For CHARACTER_LENGTH(cast(xamount as
     varchar(20))))
     end))
     From tablename

     Sample value: 0,777788 (decimal (18,6))
     If coalesce used: .777788 (varchar )
     Output of above query: 0,777788 (varchar (20))

Can anybody please tell me any other optimized way than this?

Will this query be of low performance? If yes, then why?

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