Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>  

Subj:   FastExport: Conversion issues with exported
From:   Anomy Anom

<-- 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:

     (TRIM (Case when xamount IS NULL
     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
     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>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020