CODEWITHSUNDEEP
sqloracle-databasejoin
Simone
Simone

Reputation: 131

inner join two tables on two fields - matching on month and year for the date field oracle sql

I have two tables where the date field is populated with monthly data. One of the date fields uses the start of the month and one uses the end of the month. I am using an INNER JOIN using two fields, but I can't get the formatting of the dates to work:

SELECT * FROM crd_own.ml_corp_index_data_monthly  tb1
INNER JOIN pm_own.esg_credit_factors tb2 
ON tb1.TICKER = tb2.ISSUER_TICKER
AND to_date('tbl.DATADATE', 'yyyy/mm') =to_date('tb2.AS_OF_DATE', 'yyyy/mm')

Upvotes: 0

Views: 1738

Answers (2)

GMB
GMB

Reputation: 222482

With Oracle, you can trunc both dates to month and then compare them. Please not that, as commented by @xQbert, you don't want to enclose dates in quotes because that prevents Oracle from interpretating dates properly.

SELECT * 
FROM 
    crd_own.ml_corp_index_data_monthly  tb1
    INNER JOIN pm_own.esg_credit_factors tb2 
        ON tb1.TICKER = tb2.ISSUER_TICKER
        AND trunc(tb1.DATADATE, 'month') = trunc(tb2.AS_OF_DATE, 'month')

Upvotes: 1

Simone
Simone

Reputation: 131

Thanks everyone.... this works:

SELECT * FROM crd_own.ml_corp_index_data_monthly  tb1
INNER JOIN pm_own.esg_credit_factors tb2 
ON tb1.TICKER = tb2.ISSUER_TICKER
AND trunc(DATADATE, 'month') = trunc(tb2.AS_OF_DATE, 'month')

Upvotes: 0

Related Questions