0
SELECT DR.column
FROM DB.Table RCT
LEFT OUTER JOIN DB.Table2 RC ON RCT.NBR_CLMN = RC.NBR_CLMN
LEFT OUTER JOIN DB.Table3 DR ON DR.NEW_NBR_CLMN = RC.VCHAR_COLUMN

Running the SQL code above in Teradata SQL Assistant gives me a 2620 "The format or data contains a bad character" error. After tracking down the exact cause of the problem, it seems to be due to DR.NEW_NBR_CLMN and RC.VCHAR_COLUMN being different datatypes with data such that the implicit conversion is failing.

I've tried using CAST AS VCHAR and using CAST AS BIGINT on both sides of the join like so: 

LEFT OUTER JOIN DB.Table3 DR ON CAST(DR.NEW_NBR_CLMN AS BIGINT) = CAST(RC.VCHAR_COLUMN AS BIGINT)

Unfortunately, casting as vchar returns null values from the DR table as nothing matches. Casting as bigint returns the same error as before. Part of the problem is that one column contains commas in the number while the other does (ex. "66993001968" and "66,993,001,968").

So I tried using REPLACE to remove the commas, but Teradata tells me that it isn't correct usage. More specifically, this error: "SELECT Failed. [3706] Syntax error: expected something between the 'ON' keyword and the 'REPLACE' keyword."

Here is what I am trying:

LEFT OUTER JOIN DB.Table2 RC ON REPLACE(DR.NEW_NBR_CLMN, ',', '') = RC.VCHAR_COLUMN

Any help with the overall join problem or with the more specific REPLACE problem would be greatly appreciated!

Example Data:

DR Table Example: DR Table Example

RC Table Example:

RC Table Example

Desired Result: Desired Result

PyPuppy
  • 25
  • 4
  • Sample data and desired results would help. What does the offending data look like? Why are you comparing an int column to a string? – Gordon Linoff Dec 17 '19 at 20:01
  • You have the right idea, just not the right function. Try `oreplace(DR.NEW_NBR_CLMN, ',', '')` – mechanical_meat Dec 17 '19 at 20:02
  • 1
    Also, you have two `ON`s there. – Eric Brandt Dec 17 '19 at 20:07
  • I added the sample data and desired results and removed the extra "on". Also, I tried "OREPLACE" previously, and got this error: [9881] Function 'OREPLACE' called with an invalid number or type of parameters – PyPuppy Dec 17 '19 at 21:05
  • 1
    What are the data types of those columns? `VCHAR_COLUMN` seems to indicate *VarChar* while `NEW_NBR_CLMN` seems to be *numeric*. The commas in a numeric column are displayed optionally. And if Gordon's query fixed the query it seems to indicate there's bad data in the VarChar (the CAST will silently ignore commas and thus not fail), try to find it using `select VCHAR_COLUMN from DB.Table2 RC WHERE TO_NUMBER(VCHAR_COLUMN) IS NULL` – dnoeth Dec 18 '19 at 10:07
  • You are correct @dnoeth. The code Gordon shared makes the query work, but for my own enlightenment I'll try that line that you shared and see if I can't track down that bad data. – PyPuppy Dec 18 '19 at 15:05

1 Answers1

1

You can use regexp_replace() to remove all non-digits:

ON DR.NEW_NBR_CLMN = cast(regexp_replace(RC.VCHAR_COLUMN, '[^0-9]', '') as bigint)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786