CODEWITHSUNDEEP
sqloracle-databaseleft-join
user68288
user68288

Reputation: 774

Oracle Left Join causing single-row subquery returns more than one row error

Not sure why this code is not working -- seems to be a relatively easy join.

select *
FROM VIEW_DB.VIEW1 VC0
LEFT OUTER JOIN VIEW_DB.VIEW2 VC1
ON TRIM(VC0.STRING_DATE) = TO_CHAR(VC1.CALENDAR_DAY, 'MM/DD/YYYY')

In the example VC0.STRING_DATE is a field that contains varchar data, example of Jan 1 2020 would be: "01/01/2020 " (added quotes to show the white space).

Calendar Day is a date field.

Goal is to join the string dates to date field with the left outer join intact. With an INNER join this works fine.

Additional Notes

VIEW1 -- is simply a 1:1 view of a staging table. STRING_DATE is stored as a VARCHAR(100). I have no choice in this matter. We are hoping to eliminate this issue with better ETL mapping but currently this is what we are stuck with.

VIEW2 -- CAL_DT is a DATE column. Produced by: TRUNC(to_date('2020/01/01', 'yyyy/mm/dd') -1 + N.n) as CAL_DT

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Convert the value, but on only one side:

SELECT *
FROM VIEW_DB.VIEW1 VC0 LEFT OUTER JOIN
     VIEW_DB.VIEW2  VC1
     ON TO_DATE(VC0.STRING_DATE, 'MMM DD YYYY') = VC1.CALENDAR_DAY;

Of course, this might generate a type conversion error. That is a good thing. You should learn how to store values using the correct data type. And a string is not the correct data type for a date.

Upvotes: 1

Related Questions