CODEWITHSUNDEEP
sqloracle-database
FLMD
FLMD

Reputation: 1

How to join YEARS from 2 tables with different format

I am trying to create a custom view in excel for a pivot, I cant join the YEARSF because the tb1 format is 2016-17 and tb2 is 2016/17, how can i join these, see code below... what is the appropriate way of doing this 

TB1.YEARF = TB2.YEARF

Seems to be the issue

     SELECT TB1.YEARF, 
TB1.MC,
TB1.CATEG, 
TB1.ID, 
TB1.TY, 
TB1.CAT, 
TB1.LOC, 
TB2.HD0_NAME,
TB2.HD1_NAME, 
TB2.HD2_NAME, 
TB2.HD3_NAME, 
TB2.HD4_NAME
FROM DB.TB2 TB2, DB.TB1 TB1
WHERE TB2.CAT = TB1.CAT AND TB2.LOC = TB1.LOC AND TB1.TY = TB2.TY AND TB1.YEARF = TB2.YEARF AND TB1.ID = TB2.ID

Upvotes: 0

Views: 69

Answers (2)

O. Jones
O. Jones

Reputation: 108686

It happens that both Oracle and MySQL have REPLACE() functions.

If you change the part of your query that reads 

 AND TB1.YEARF = TB2.YEARF AND

to

 AND TB1.YEARF = REPLACE(TB2.YEARF, '/', '-') AND

you may be able to join these tables. It's not going to be fast.

In general, to do this kind of inexact matching, you have to

  1. figure out the rules for matching. For example, if you want abcd/ef to match abcd-ef then you can use what I wrote above. If you want abcd/ef to match abcd, that's a different rule.

  2. Write a SQL expression to implement your rules.

But, you know, step 1 must come before step 2.

If all you care about is matching the abcd parts of abcd/ef and abcd-ef you can write a rule for that.

 AND SUBSTR(TB1.YEARF, 1, 4) = SUBSTR(TB2.YEARF, 1, 4) AND

Upvotes: 1

Pants
Pants

Reputation: 679

WHERE Format(TB1.YEARF, "yyyy-dd") = Format(TB2.YEARF, "yyyy-dd")

Upvotes: 0

Related Questions