TIPS TO CONVERT DECIMAL TO VARCHAR

Database
Enthusiast

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

Tags (1)
7 REPLIES
Junior Contributor

Re: TIPS TO CONVERT DECIMAL TO VARCHAR

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.

Enthusiast

Re: TIPS TO CONVERT DECIMAL TO VARCHAR

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

Junior Contributor

Re: TIPS TO CONVERT DECIMAL TO VARCHAR

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;'
Enthusiast

Re: TIPS TO CONVERT DECIMAL TO VARCHAR

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.

Enthusiast

Re: TIPS TO CONVERT DECIMAL TO VARCHAR

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

Junior Contributor

Re: TIPS TO CONVERT DECIMAL TO VARCHAR

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 = '''',.'''''')
Enthusiast

Re: TIPS TO CONVERT DECIMAL TO VARCHAR

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