1

I am very new to Teradata and SQL in general. I need to create a table by combining data from three tables. I was able to successfully join two of them. I am not able to write the joining condition for the third table properly. Here is the code:

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 t.cold = cast(u.colm as decimal)
order by 3
where substr(cast(s.cola as varchar(10)),6,2) = 11 and substr(cast(s.cola as varchar(10)),1,4) = 2017 and substr(cast(s.cola as varchar(10)),9,2) between 06 and 10

The error I am getting is:

[Teradata Database] [2620] The format or data contains a bad character.

I think the problem is with the line: on t.cold = cast(u.colm as decimal). The u.colm is of type VARCHAR(50) while t.cold is of type DECIMAL(10, 0). I believe I have casted it properly. Please help.Thanks in advance.

skr
  • 914
  • 3
  • 18
  • 35

1 Answers1

5

There's some bad data in u.colm.

Depending on your Teradata release you can check it using 

WHERE u.colm > '' AND TRYCAST(u.colm as decimal(10,0)) ISNULL

or 

WHERE u.colm > '' AND TO_NUMBER(u.colm) IS NULL

You can also use those in the join-condition, e.g.

on t.cold = trycast(u.colm as decimal(10,0))

Don't forget to add the precision of the decimal, as it defaults to (5,0).

Your WHERE_condition is strange, what's the datatype of s.cola? Seems it's a string with a date yyyy-mm-dd in it. Try 

WHERE trycast(s.cola as date) between date '2017-11-06' and date '2017-11-10'

Finally the ORDER BY should be placed after WHERE.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Thank you for your answer. It turns out that `u.colm` has a few values which contains both alphabets and numbers. I fixed it by casting `t.cold` as `varchar(50)`. The `s.cola` is date, but the above line didn't work. Thank you for the correction on `order by`. – skr Dec 22 '17 at 05:26
  • The date issue in `where` condition was resolved when I tried `cast` instead of `trycast`. – skr Dec 22 '17 at 05:29