All Forums UDA
Seneg 3 posts Joined 02/14
10 Feb 2014
Cast and format

Hi !
First, i want to apologize for my english. I'm french and my english is very bad.
I have a problem with an extract with MLoad.
When i try to converte a VARCHAR like '70,37' in DECIAML(6,2) with :
CAST('70,37' as DECIMAL(6,2) format 'ZZZZ,ZZ')
Teradata return 7037,00 not 70,37
The probleme is this character ',' in the data. With a dot it's ok. But i can't change the data ...
is existe a format to chage that ?
 
Thanks a lot for your help !

dnoeth 4628 posts Joined 11/04
10 Feb 2014

Hi Seneg,
don't apologize, I'm german and my french is worse than your english :-)
The format doesn't help because the comma is used as a thousand separator.
If you're on TD14 you can switch to Oracle's TO_NUMBER which is a bit more flexible:
CAST(TO_NUMBER(col, '9999D99','NLS_NUMERIC_CHARACTERS = '',.''') AS DECIMAL(6,2))
 
Otherwise if there's always two fractional digits you can simply CAST(col AS DECIMAL(8.0)) / 100.00

Dieter

Seneg 3 posts Joined 02/14
11 Feb 2014

Hi ,
Thank you for your help, but this doesn't work.
We are in TD13 and i think TO_NUMBER() is not in.
Have you a solution for TD13 ?

dnoeth 4628 posts Joined 11/04
11 Feb 2014

As I wrote, if there's always the same number of fractional digits with the string you can do 

CAST(col AS DECIMAL(8.0)) / 100.00 -- 1000.000 for 3 digits, etc.

If you're on TD13 you still might have the oTranslate or oReplace UDF:

CAST(oTranslate(col, ',', '.') AS DECIMAL(6.2))

Or brute force:

CAST(     SUBSTRING(col FROM 1 FOR POSITION(',' IN x) - 1) 
|| '.' || SUBSTRING(col FROM       POSITION(',' IN x) + 1)) AS DEC(6,2))

 

Dieter

Seneg 3 posts Joined 02/14
11 Feb 2014

I have choose this solution :

CAST(CASE WHEN position(',' IN :COL02) = 0 
				THEN :COL02 
				ELSE substr(:COL02,1,position(',' IN :COL02)-1)||'.'||substr(:COL02,position(',' IN :COL02)+1) 
			END 
		as DECIMAL(6,2) format 'ZZZZZZ,ZZ')

Because if there are a value without decimal, Teradata return a error.
Thanks for your help, i close the discution.

YIAPIPI 9 posts Joined 02/14
27 Feb 2014

hi All,
i would like to face a new topic about date fields subtraction in TERADATA 12.0 environment;
briefly i have two field containig data, the first one defined as "data" and the second one as "varchar(8)".
executing my query as follow :
 
SELECT top 100
CLI.A40_CLNT_LCL_ID
, CLI.DT_RFRM
, CLI.A36_COUNT_RTN_DT
, cast(cast(CLI.A36_COUNT_RTN_DT as date format 'yyyymmdd') as date format 'yyyy-mm-dd')
, (CLI.DT_RFRM - substr (CLI.A36_COUNT_RTN_DT,2,8)) as diff_date
FROM FEED_BMRC.CAPRI_CLIENT CLI
 
the result i got in diff_data field is like :
A40_CLNT_LCL_ID   DT_RFRM       A36_COUNT_RTN_DT    cast date         diff_date
301260541              31/12/2033   20131210                     10/12/2013     1200021,00
 
instead of the number vof day between dtes.
someone's may help me in this problem?
thank's a lot for your attention and courtesy in answering
 
K Regards
Pio

 

dnoeth 4628 posts Joined 11/04
27 Feb 2014

Hi Pio,
what do you expect?
A DATE minus a DATE results the number of days difference,but you substract a string from a date:
DATE '2033-12-31' - '0131210'
Because this is not defined for dates and because minus is a numeric operator both operands are automatically casted to a float and the substracted.

, cast(cast(CLI.A36_COUNT_RTN_DT as date format 'yyyymmdd') as date format 'yyyy-mm-dd') as datecol
, CLI.DT_RFRM - datecol as diff_date

Btw, of course a DATE should not be stored as a string (and not as a VARCHAR instead of CHAR(8))

Dieter

YIAPIPI 9 posts Joined 02/14
28 Feb 2014

Hi Dieter
thank's, thank's , thank's a LOT
I am astonished for my incompetency.
 
K Regards

david.craig 73 posts Joined 05/13
28 Feb 2014

The Teradata Database has i18n formatting and parsing capabilities going back to V2R5. Start by defining your French Locale as an SDF with the TDLoacleDef utility. See the reference manuals for more details.

YIAPIPI 9 posts Joined 02/14
25 Mar 2014

hi All,
i came back on topic about date fields conversion in TERADATA 12.0 environment;
briefly i have primary to convert a char(10) "NER.DT_FNE_VLD_RAT"  field containing a data like 26.01.2013 but receive an error message " precision error in float type constant or during implicit conversions"  executing my query as follow :
 
SELECT
CLI.A40_CLNT_LCL_ID, CLI.A4_CORP_NAME, CLI.CD_SAE, CLI.CD_RAE, CLI.MERC, CLI.COD_MDLL
, CLI.COD_MOTIVAZ, CLI.DT_RFRM,
cast(cast(NER.DT_FNE_VLD_RAT as date format 'yyyymmdd') as date format 'yyyy-mm-dd') as Data_Scad_Rating
, cast(cast(NER.DT_INI_VLD_RAT as date format 'yyyymmdd') as date format 'yyyy-mm-dd') as Data_Val_Rating
, CLI.DT_RFRM - Data_Val_Rating as Vetusta_Rat
, NER.DESCR_RAT
, NER.CLASSE_RAT
FROM
FEED_BMRC.CAPRI_CLIENT CLI, DXDWH_DB.DWH_FD FID, DXDWH_STG.NERATING NER

someone's may help me in this problem?
thank's a lot for your attention and courtesy in answering

K Regards
Pio

Adharssh 36 posts Joined 08/13
12 Apr 2014

Hi,

SELECT
CLI.A40_CLNT_LCL_ID, CLI.A4_CORP_NAME, CLI.CD_SAE, CLI.CD_RAE, CLI.MERC, CLI.COD_MDLL
, CLI.COD_MOTIVAZ, CLI.DT_RFRM,
cast(otranslate(NER.DT_FNE_VLD_RAT,'.','-') as date format 'yyyy-mm-dd') as Data_Scad_Rating
, cast(otranslate(NER.DT_INI_VLD_RAT,'.','-') as date format 'yyyy-mm-dd')  as Data_Val_Rating
, CLI.DT_RFRM - Data_Val_Rating as Vetusta_Rat
, NER.DESCR_RAT
, NER.CLASSE_RAT
FROM
FEED_BMRC.CAPRI_CLIENT CLI, DXDWH_DB.DWH_FD FID, DXDWH_STG.NERATING NER;

Thanks & Regards,
Adharssh.

Share the Knowledge. Feel the Happiness, When you share/Teach it.

22 Nov 2015

*******urgent***************
Hello every one ...
I need a help 
I have two tables and there is common field between those two table that is 'DP_INSERT_DATE' and the format of the date is "YYYY-MM-DD.T.MM:SS:(time zone also)" 
I need to join the tables using DP_INSERT_DATE and get the information....
Can you please help in the query thanks alot.
You can mail me at vinit.tippu@gmail.com
 
 
******Urgent*********

sandy1241 1 post Joined 03/16
28 Mar 2016

I have below decimal values, 
0012.15
12457.4578
12.445
any way to cast as varchar and return excatly the same values in one select statement?

dnoeth 4628 posts Joined 11/04
28 Mar 2016

You can't have 0012.15 in a Decimal column, there are no leading zeros unless you apply a FORMAT.
Regarding the variable number of fractional digits the easiest way is this

TRIM(col (FORMAT 'FN9'))

 

Dieter

spetmp 1 post Joined 01/16
11 Aug 2016

hi,
I want to diplay 011 as 'zero one one ' in teradata. can you help me out !!
eg: 011- i need to display as 'zero one one'
012- i need to display as 'zero one two' like that way
 
Regards,
Vijaykumar

You must sign in to leave a comment.