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

Question

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

I don't see how this could would raise the single-row subquery returns more than one row error. — GMB
care to share column types and if they're VARCHAR2, formats ? I smell a trunc() or two may be need. — BigMike
The query looks okay. The error must be in one of the views. — Thorsten Kettner

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-09-04T14:56:44

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.

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

1 Answers