Archives of the TeradataForum
Message Posted: Thu, 21 Jun 2012 @ 15:12:23 GMT
<-- Anonymously Posted: Thursday, June 21, 2012 07:12 -->
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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|