CODEWITHSUNDEEP
sqlinner-jointeradataon-clause
skr
skr

Reputation: 944

Teradata - Adding preceding zero and casting as varchar(50)

I have the following query:

SELECT
  s.cola, s.colb, t.colc, t.cold, u.cole, u.colf, u.colg, u.colh, u.coli, u.colj, u.colk, u.coll 
FROM table1 s
INNER JOIN table2 t
  ON s.colb = t.colc
INNER JOIN table3 u
  ON u.colm = CAST(t.cold AS varchar(50))
WHERE cast(s.cola as date) between date '2017-11-06' and date '2017-11-10'
ORDER BY 3

Here, in the last joining condition, u.colm is of type varchar(50) and t.cold is of type decimal(10, 0). I cannot cast u.colm as decimal(10, 0) because some of the outdated values in that column are not purely numbers. Now, if I run the query as shown above, it will return a blank table because the common values in u.colm has a preceding/leading zero while t.cold doesn't have that zero. I tried the following:

1) on u.colm = '0' + cast(t.cold as varchar(50))

This gave the error: [Teradata Database] [2620] The format or data contains a bad character.

2) on u.colm = right('0000000000' + cast(t.cold as varchar(50)), 50)

This gave the error: [Teradata Database] [9881] Function 'TD_RIGHT' called with an invalid number or type of parameters

The question, answers and comments in link1 will provide some more context. I am pretty new to Teradata and don't have much idea on how to resolve this issue. Kindly help.

Upvotes: 0

Views: 7097

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I believe Teradata supports the LPAD() function, so you can use:

u.colm = lpad(t.cold, 10, '0')

Upvotes: 2

Related Questions