queries on casting in teradata

General

queries on casting in teradata

Hi,

I am a newbie in teradata and so am not familiar with casting/ formatting concepts...

can you please clarify the below queries...

1. I have an export using below syntax

sel abc (char(10)), def (char(4)) 
from
 (sel abc, count(*) as def from xyz)

but def being Integerand since i've declared it as char, no data is being displayed on execution of the query. how do i need to correct it..

2.  see below my query - for export query

sel yr (char(04))

from (sel  extract(year from servic_dte) as yr from abc)

in this scenario, no date is being pulled/ displayed. this is coz' being date field and displaying it as Char causes the issue. how do i need to do casting...

3. can you share me a link which shares tutorial/ explanation on casting/ formatting.

thanks in advance,

Regards,

Sam

Tags (3)
2 REPLIES
Enthusiast

Re: queries on casting in teradata

Samath,

In the first query, as the count is Integer, and integer requires 11 char for its representation, when integer is converted into Char it is right aligned and result is only left sid 4 characters.

you can change it like this: 

sel abc (char(10)), TRIM(def (CHAR(11))) (CHAR(4))
from
(sel abc, count(*) as def from xyz)

Similarly for the second query, the year returned is integer, so

sel TRIM(yr (CHAR(11))) (char(04))

from (sel extract(year from servic_dte) as yr from abc)
Khurram

Re: queries on casting in teradata

hi,

i was able to google to get the below answers

1)

sel 
abc (char(10))
, cast(def  as char(4))
 from
(sel abc, count(*) as def from xyz)

2)

sel 
cast (yr as char(04))
 from
(sel  extract(year from servic_dte) as yr from abc)

thanks,

Sam