2

I'm trying to add some fields to an existing report that runs just fine. To add these fields, I need to join to another table. The problem is the field is a different type on each table

Table A / FieldA = Varchar (20)
Table B / FieldB = Decimal (19,0)

This is the join I have:

inner join TableA ta on ta.FieldA = b.FieldB

With this join, I get a SELECT Failed [3754] Precision error in FLOAT type constant or during implicit conversions.

I'm thinking I have to use a CAST statement like this:

inner join TableA ta on ta.FieldA = cast(b.FieldB as Varchar (20))

When I run the report now, I don't get any results and I'm expecting at least 1 row.

Any help with the inner join would be greatly appreciated. Thanks.

Tripp Kinetics
  • 4,960
  • 2
  • 21
  • 34
Shaves
  • 778
  • 2
  • 13
  • 33

2 Answers2

1

Your join on ta.FieldA = b.FieldB will result in an automatic typecase from VarChar to Float. When you actually got 19 digits it will overflow the 15 digits precision of a Float.

A problem when you cast(b.FieldB as Varchar (20)) might be the default fractional seperator, i.e. 123 will return 123..

You might try on ta.FieldA = TRIM(TRAILING '.' FROM b.FieldB) but the best way is probably on TO_NUMBER(ta.FieldA) = b.FieldB. This will also prevent type conversion error as it simply returns NULL for bad data.

dnoeth
  • 54,996
  • 3
  • 29
  • 45
  • @dnoeth............thanks for the suggestion. this also worked for me. – Shaves Apr 11 '18 at 21:06
  • I know it's convenient sometimes, but implicit casting is just evil. – Andrew Apr 12 '18 at 14:23
  • If doing this at scale or multiple times, it may be worth taking the one-off hit of converting the join column's datatype to that of the other table. After this, datatypes will match so no conversion will be needed as part of the JOIN operation. – v0rl0n Apr 25 '18 at 14:30
0

Instead of the cast, try:

TO_CHAR(b.FieldB)
Tripp Kinetics
  • 4,960
  • 2
  • 21
  • 34