Oracle issue with a left outer join subquery

Question

I have a sql which is failing in a left outer join subquery with

ORA-01427: single-row subquery returns more than one row

Here is the left outer join query fragment:

 LEFT OUTER JOIN (aa.location) LOCATION
    ON (location_info_300.client_num = location.client_num
    AND location_info_300.source = location.source
    AND location_info_300.location_code = location.location_code
    AND 1 =
           (SELECT ROW_NUMBER()
                   OVER(PARTITION BY location_code, client_num, SOURCE
                        ORDER BY expiry_date DESC)
                      AS rec_order_by_expiry_desc
              FROM aa.location l2
             WHERE location.client_num = l2.client_num
               AND location.source = l2.source
               AND location.location_code = l2.location_code
               AND l2.expiry_date >=
                      TO_DATE('01-JAN-' || location_info_300.reporting_year,
                              'DD-MON-YYYY')
               AND l2.effective_date <=
                      TO_DATE('31-DEC-' || location_info_300.reporting_year,
                              'DD-MON-YYYY')))

I tried fixing it by doing the following change in the last AND criteria:

1 = 
(SELECT rec_order_by_expiry_desc
  FROM (SELECT ROW_NUMBER() OVER (PARTITION BY LOCATION_CODE, CLIENT_NUM, SOURCE ORDER BY EXPIRY_DATE DESC) AS REC_ORDER_BY_EXPIRY_DESC
   FROM aa.LOCATION l2
  WHERE location.CLIENT_NUM = l2.CLIENT_NUM
    AND location.SOURCE = l2.SOURCE
    AND location.LOCATION_CODE = l2.LOCATION_CODE
    AND l2.EXPIRY_DATE >= TO_DATE('01-JAN-'||location_info_300.REPORTING_YEAR,'DD-MON-YYYY')
    AND l2.EFFECTIVE_DATE <= TO_DATE('31-DEC-'||location_info_300.REPORTING_YEAR,'DD-MON-YYYY'))
WHERE rec_order_by_expiry_desc = 1)

But now I am getting the following error:

ORA-00904: "LOCATION_INFO_300"."REPORTING_YEAR": invalid identifier

I am not sure what else to try. I hope someone does though!

Answer 1

I think you're basically checking if the row exists in the subquery? If so, then just do an EXISTS:

   LEFT OUTER JOIN (aa.location) LOC
                                 ON (location_info_300.client_num = loc.client_num
                                     AND location_info_300.source = loc.source
                                     AND location_info_300.location_code = loc.location_code
                                     AND exists (SELECT null
                                                 FROM   aa.location l2
                                                 WHERE  loc.client_num = l2.client_num
                                                 AND    loc.source = l2.source
                                                 AND    loc.location_code = l2.location_code
                                                 AND    l2.expiry_date >= TO_DATE('01-JAN-' || location_info_300.reporting_year, 'DD-MON-YYYY')
                                                 AND    l2.effective_date <= TO_DATE('31-DEC-' || location_info_300.reporting_year, 'DD-MON-YYYY')))

N.B. I changed the alias of the aa.location table, just to avoid any possible conflicts between the outer and sub-query's aa.location tables (much better to make sure that aliases aren't the same as existing identifier names to avoid any potential scope clash issues. Also, it makes it easier to understand when you read the query).