All Forums Database
rubenjdc 13 posts Joined 04/16
20 May 2016
TIPS TO CONVERT DECIMAL TO VARCHAR

Hi everyone, i need help with something (Im noob using TD, and sorry my english)
I´m trying to prepare a tpt script to export some data to a delimited file
In the table i have columns in decimal type, tpt needs cast every column what dont be varchar to varchar, there's no problem
The problem its when i cast decimal to varchar... when the number its 0.01 for example TD cast data like this .01, this its a problem for me.
I want to ask some way to cast decimal to varchar without lose the first 0 when the number < 1
I have some fix for it, but its not really efficient because it put an extra 0 althought the column needs it or not.
But i have another problem with it, when i have a negative number like -0.50, TD casts it like this: -.50
I have a script what makes a replace but, TPT dont recognize oreplace and crash the job, this is the script:
select
CASE
WHEN substr(cast(discount as varchar(10)),1,1)='-' THEN oreplace(cast(discount as varchar(10)),'-','-0')
ELSE oreplace(cast(discount as varchar(10)),'.','0.')
end as discount
from table.datalab;
 
Definitely if i could have a tip to convert decimal to varchar without the error what i commented i could save all this T.T
 
Thanks in advance!
RubenDC

dnoeth 4628 posts Joined 11/04
20 May 2016

When you specify FORMAT = 'Delimited' in your TPT you don't have to cast every columns to VarChar, it's done automatically, but using default formats.
 
In your case you don't want the default format, so you must specify it:

TRIM(discount (FORMAT '-(10)9.99'))

-(10) -> repeat the minus sign 10 times = a floating minus before the first digit
9.99  -> at least one digit plus two fractional digits 

For larger DECIMALs you might adjust the FORMAT with more leading digits.
 
The TRIM is automatically casting to a VarChar, it's shorter syntax and you don't have to think about the correct length.

Dieter

rubenjdc 13 posts Joined 04/16
23 May 2016

Hi Dieter, thanks for your answer.
Your tip works, and i can make a good select, but in the select statement in my tpt i insert the select what i test on Teradata Studio and show me this error:
 
TPT_INFRA: TPT03589: in SELECT part. Check with the syntax below.

APPLY {dml-list} TO OPERATOR
    (operator-name {[n]} {ATTR (attribute-list)})
SELECT {(column-list) | *} FROM OPERATOR
    (operator-name {[m]} {ATTR (attribute-list)});

Comma separated multiple APPLY statements can be specified.
UNION ALL separated multiple SELECT statements can be specified.
I put this select:
SELECT cast(substr(col1,1,9) as varchar(9)), col2, col3, col4, col5, col6, trim(col7 (format '-(10)9.99')), trim(col8 (format '-(10)9.99')), cast(col9 as varchar(64)), trim(col10 (format '-(10)9.99')), trim(col11 (format '-(10)9.99')), trim(col12 (format '-(10)9.99')), cast(col13  as varchar(64)), cast(col14 as varchar(64)), cast(col15 as varchar(64)), col16, trim(col17 (format '-(10)9.99')) FROM table.datalab;
 
Thanks for all
 

dnoeth 4628 posts Joined 11/04
23 May 2016

You need to double each single quote because the APPLY statement is s string:

'SELECT cast(substr(col1,1,9) as varchar(9)), col2, col3, col4, col5, col6,
 trim(col7 (format ''-(10)9.99'')), trim(col8 (format ''-(10)9.99'')), 
cast(col9 as varchar(64)), trim(col10 (format ''-(10)9.99'')), 
trim(col11 (format ''-(10)9.99'')), trim(col12 (format ''-(10)9.99'')), 
cast(col13  as varchar(64)), cast(col14 as varchar(64)),
 cast(col15 as varchar(64)), col16, trim(col17 (format ''-(10)9.99'')) 
FROM table.datalab;'

 

Dieter

rubenjdc 13 posts Joined 04/16
23 May 2016

Omg i didnt realise about that, i have thousands things in my mind and i dindt think about this.
Thanks so much Dieter, it works.
 

rubenjdc 13 posts Joined 04/16
23 May 2016

One last thing
I need to replace dot for comma in numbers and i use this select:
SelectStmt = 'SELECT cast(substr(col1,1,9) as varchar(9)) as col1, col2,col3,col4, col5, col6, oreplace(trim(col7 (format ''-(12)9.99'')),''.'','','') , oreplace(trim(col8 (format ''-(12)9.99'')),''.'','','') ,cast(col9 as varchar(64)), oreplace(trim(col10 (format ''-(12)9.99'')),''.'','',''), oreplace(trim(col11 (format ''-(12)9.99'')),''.'','',''), oreplace(trim(col12 (format ''-(12)9.99'')),''.'','','') ,cast(col13  as varchar(64)), cast(col14 as varchar(64)),cast(col15 as varchar(64)), col16, oreplace(trim(col17 (format ''-(12)9.99'')),''.'','','')  FROM DL_CLIENTES.VW_CL_FACT_DIARIA;',
In TD studio theres not problem with the query but in tpt show the following error:
 
TPT_INFRA: TPT02638: Error: Conflicting data length for column(7) - col7. Source column's data length (64) Target column's data length (8000).
 
Its like didnt make fine the transformation

 

dnoeth 4628 posts Joined 11/04
23 May 2016

Now it's getting ugly :)
oREPLACE is a UDF and return a VARCHAR(8000), you can either add another CAST:

CAST(oTranslate(trim(col7 (format '-(12)9.99')),'.',',') AS VARCHAR(64)

(I also switched to oTRANSLATE instead of oREPLACE)
or you switch to TO_CHAR, where you can specify the seperators, but now doubling quotes gets really ugly:

to_char(col7, ''99999999999.99'', ''NLS_NUMERIC_CHARACTERS = '''',.'''''')

 

Dieter

rubenjdc 13 posts Joined 04/16
24 May 2016

Good morning!
I did the last try today with your answer and it works!!!
I used the another cast option and tpt export
Thanks so much for your help Dieter!!

You must sign in to leave a comment.