All Forums General
sammath 2 posts Joined 09/13
25 Jun 2014
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

samuel
M.Saeed Khurram 544 posts Joined 09/12
25 Jun 2014

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

sammath 2 posts Joined 09/13
25 Jun 2014

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

samuel

You must sign in to leave a comment.