How to left join with conditions in Toad Data Point Query Builder?

Question

I'm trying to build a query in Toad Data Point. I have a subquery that has a row number to identify the records I'm interested in. This subquery needs to be left joined onto the main table only when the row number is 1. Here's the query I'm trying to visualize:

SELECT distinct E.EMPLID, E.ACAD_CAREER
FROM PS_STDNT_ENRL E
LEFT JOIN (
  SELECT ACAD_CAREER, ROW_NUMBER() OVER (PARTITION BY ACAD_CAREER ORDER BY EFFDT DESC) as RN
  FROM PS_ACAD_CAR_TBL
) T on T.ACAD_CAREER = E.ACAD_CAREER and RN = 1

When I try to replicate this, the row number condition is placed in the global WHERE clause. This is not the intended functionality because it removes any records that don't have a match in the subquery effectively making it an inner join.

Here is the query it's generating:

SELECT DISTINCT E.EMPLID, E.ACAD_CAREER, T.RN
  FROM SYSADM.PS_STDNT_ENRL E
       LEFT OUTER JOIN
       (SELECT PS_ACAD_CAR_TBL.ACAD_CAREER,
               ROW_NUMBER ()
                  OVER (PARTITION BY ACAD_CAREER ORDER BY EFFDT DESC)
                  AS RN
          FROM SYSADM.PS_ACAD_CAR_TBL PS_ACAD_CAR_TBL) T
          ON (E.ACAD_CAREER = T.ACAD_CAREER)
 WHERE (T.RN = 1)

Is there a way to get the query builder to place that row number condition on the left join instead of the global WHERE clause?

Answer 1

I found a way to get this to work.

1. Add a calculated field to the main table with a value of 1.

2. Join the row number to this new calculated field.

Now the query has the filter in the join condition instead of the WHERE clause so that it joins as intended. Here is the query it made:

SELECT DISTINCT E.EMPLID, E.ACAD_CAREER, T.RN
  FROM SYSADM.PS_STDNT_ENRL E
       LEFT OUTER JOIN
       (SELECT PS_ACAD_CAR_TBL.ACAD_CAREER,
               ROW_NUMBER ()
                  OVER (PARTITION BY ACAD_CAREER ORDER BY EFFDT DESC)
                  AS RN
          FROM SYSADM.PS_ACAD_CAR_TBL PS_ACAD_CAR_TBL) T
          ON (E.ACAD_CAREER = T.ACAD_CAREER) AND (1 = T.RN)