1

Trying to join two tables on a column referencing diagnoses. One table uses the actual diagnosis code (aka has decimal), but the other table uses the diagnosis code sans decimal/period. Ex: A43.21 v A4321 or 553.1 v 5531

LEFT JOIN DB_VIEW.Reference_Diagnosis_Table RDT
ON RDT.Diagnosis_Code = SANS.Diagnosis_Code

But obviously they aren't equal to eachother... is there a way to write this so it interprets the RDT.Diagnosis_Code without having it? (FYI I do not have the ability to alter the existing data stored within the warehouse)

DATARN
  • 11
  • 2
  • As far as I know, this is not going to work, in order for you to be able to join tables the data has to be the same 1.111 is not the same as 1. I would suggest trying to find another common field to join on and take a look at https://www.w3schools.com/sql/sql_join.asp – Husk Rekoms Jun 18 '19 at 20:43
  • 2
    I am not familiar with this type of coding but if they are not used to do math operations, those identifiers should be treated as char or varchar values. If the matching obeys to a rule you can try to see if the matching can be done using a function that implements the rule. And if the matching is something fixed you may use an additional relation table to hold the mappings between the two codes and include this table in the join of the other two. – Juan Jun 19 '19 at 12:06
  • @HuskRekoms & DATARN Joins are on conditions, not columns. Write a condition for when you want a match. PS What language & system? PS (Obviously--) This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Jun 20 '19 at 01:19
  • Possible duplicate of [How do you strip a character out of a column in SQL Server?](https://stackoverflow.com/questions/983417/how-do-you-strip-a-character-out-of-a-column-in-sql-server) – philipxy Jun 20 '19 at 02:32
  • @philipxy. We use SQL to extract data from Teradata. And I am trying to join on the condition that the data in these two columns match... but they don't due to the decimal. And this isn't a FAQ. I looked through several Q&As that referenced select statements to change the data within the tables (which i can not do), I can manipulate the data within a query. but i've only seen that work within the select statement and not within the join. I would appreciate constructive responses that may actually help me with the problem. – DATARN Jun 20 '19 at 13:12
  • Replace is a string function you can call anywhere a scalar can appear. `join on replace(RDT.Diagnosis_Code, '.', '') = SANS.Diagnosis_Code` Optimizing DDL & DML for joins with function calls in conditions is also a faq. It seems like it might help if you read more about the basics of SQL expressions. – philipxy Jun 20 '19 at 19:54

0 Answers0