Usage of Cast to add '0000' in column

Teradata Debugger
Enthusiast

Usage of Cast to add '0000' in column

Hi,

 

Scenario is to make data in column 16 digit irrespective of data flowing from file into table.

For example:

Create table T1

( col1 varchar(16) not null,

col2 varchar(16)

)

 

File has data :

col1       col2

ABC  123456789

DEF   456789

 

Output should be :

 

ABC  0000000123456789

DEF  0000000000456789

 

Idea is to have col2 (16 digit) irrespective of any data in file. We have to pad '0' from left side if there are not sufficient numbers.

 

I have tried the below query to perform it :

insert into ()

values (

CASE WHEN :col2 IS NOT NULL THEN
       CAST(CAST(:col2 AS FORMAT '9(16)') AS CHAR(16))  
       ELSE NULL

)

 

It is throwing error "UTY0805 RDBMS failure, 3527: Format string '9(16)' has combination of  numeric, character  and GRAPHIC values". But this operations works in SELECT qeuery directly. I belive it is not working in BTEQ. Please advise.

 

Hope this give fair understanding of problem. Appreciate your quick response. Thanks         

 

 


Accepted Solutions
Enthusiast

Re: Usage of Cast to add '0000' in column

Thanks to all the amazing people for their continuous support and help.

I got the solution for this problem. Hence i am closing this request.

 

Solution is : LPAD (COL1, 16, '0')

 

Many Thanks.

1 ACCEPTED SOLUTION
4 REPLIES
Enthusiast

Re: Usage of Cast to add '0000' in column

Thanks to all the amazing people for their continuous support and help.

I got the solution for this problem. Hence i am closing this request.

 

Solution is : LPAD (COL1, 16, '0')

 

Many Thanks.

Junior Contributor

Re: Usage of Cast to add '0000' in column

Of course you should switch to a CHAR(16) instead of VARCHAR(16) now.

Enthusiast

Re: Usage of Cast to add '0000' in column

Thank you @dnoeth. But it is working for me in VARCHAR(16).

Junior Contributor

Re: Usage of Cast to add '0000' in column

Of course it's working, but a VARCHAR has two additional bytes overhead, i.e. 18 vs. 16 bytes.