0

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.

Ali Azam
  • 2,047
  • 1
  • 16
  • 25
skr
  • 914
  • 3
  • 18
  • 35
  • 2
    Why don't you use my proposed way to remove the bad data before the join? `WHERE u.colm > '' AND TO_NUMBER(u.colm) IS NULL`. Is there a consistent number of leading zeroes? Can you show some existing data? – dnoeth Dec 22 '17 at 11:54
  • The table from which `u.colm` comes from has numbers that belong to three categories. The category which is supposed to match with `t.cold` has preceding zero and are 10 digits in all cases. The `t.cold` values are all 9 digits since they don't have preceding zeros. – skr Dec 22 '17 at 12:02
  • I am trying to incorporate your `where` clause. I will update after that. Thank You. – skr Dec 22 '17 at 12:06
  • @dnoeth : It looks like adding your `where` clause resolved the issue. Can you please post it as an answer so that I can accept it? Thank You. – skr Dec 22 '17 at 12:22
  • 2
    It's the same WHERE I posted on your previous question :-) – dnoeth Dec 22 '17 at 16:18

1 Answers1

2

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

u.colm = lpad(t.cold, 10, '0')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • It says: `[Teradata Database] [9881] Function 'LPAD' called with an invalid number or type of parameters` – skr Dec 22 '17 at 11:39