convert DB2 sql code to Teradata

Database
Enthusiast

convert DB2 sql code to Teradata

Hi I am trying to covert the below sql code to run in teradata. The prod_acct_no is an integer field and the access_medium_no is a varchar(19).

I have a tried the teradata substring and char_length but getting some errors. Doesn't like the integer field.

 

            substr(digits(b.prod_acct_no), length(digits(b.prod_acct_no)) - 8) ||
                substr(rtrim(a.ACCESS_MEDIUM_NO),length(rtrim(a.ACCESS_MEDIUM_NO))+1 -4,4)  as PROD_KEY,


Accepted Solutions
Teradata Employee

Re: convert DB2 sql code to Teradata

Teradata does not have a digits function, so you need to use Teradata casting to convert the integer to character.  Assuming an integer is 9 digits:

 

substr(b.prod_acct_no (format'9(9)') (char(9)), length(b.prod_acct_no (format'9(9)') (char(9))) - 8) ||
substr(rtrim(a.ACCESS_MEDIUM_NO),length(rtrim(a.ACCESS_MEDIUM_NO))+1 -4,4) as PROD_KEY,

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: convert DB2 sql code to Teradata

Teradata does not have a digits function, so you need to use Teradata casting to convert the integer to character.  Assuming an integer is 9 digits:

 

substr(b.prod_acct_no (format'9(9)') (char(9)), length(b.prod_acct_no (format'9(9)') (char(9))) - 8) ||
substr(rtrim(a.ACCESS_MEDIUM_NO),length(rtrim(a.ACCESS_MEDIUM_NO))+1 -4,4) as PROD_KEY,

Senior Apprentice

Re: convert DB2 sql code to Teradata

Instead of trying to rewrite this code exactly as-is it might be easier to explain what it's actually returning (with examples) and write it from scratch.

Teradata Employee

Re: convert DB2 sql code to Teradata

oops wrong understanding :)