Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


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

<-- 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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016