Can't select column I used in join-on statement

Question

I'm modifying an SQL query so it will be database agnostic (and therefore also would work in SQL Server). This means I have replaced a join-using with a join-on. This worked, but when I placed that sub-query back into its parent I get an error.

I am getting an ORA-00904: study_event_id: invalid identifier error on the SELECT of a column I used in the ON statement of the JOIN.

The code below is stripped down a lot to the minimum where I get the error, so the subqueries and aliases need to be preserved as much as possible.

SELECT
    study_event_id
FROM
    (
        SELECT
            *
        FROM
            (
                SELECT
                    ea.*,
                    sbj.subject_id
                FROM
                    v_study_event ea,
                    v_subject     sbj
            ) lefttable
            LEFT OUTER JOIN (
                SELECT
                    *
                FROM
                    v_subject_current_event_status
            ) righttable ON righttable.study_event_id = lefttable.study_event_id
        
    ) tbl

I can select every other column, but the column I joined on can't be identified.

The column does appear when I SELECT *.

I have tried to specify it with all the above aliases: tbl, lefttable and righttable.

I just don't understand why that one column can't be selected.

In the full query this column is selected with a bunch more, and there are two columns in the join-on statement The other column also can't be selected, but I don't need that one further in the complete query.

Answer 1

SELECT ea.*,
       sbj.subject_id
FROM   v_study_event ea,
       v_subject     sbj

Is the same as:

SELECT ea.*,
       sbj.subject_id
FROM   v_study_event ea
       CROSS JOIN v_subject sbj

and:

LEFT OUTER JOIN (
  SELECT *
  FROM   v_subject_current_event_status
) righttable

Is the same as:

LEFT OUTER JOIN v_subject_current_event_status righttable

And:

SELECT *
FROM   ( ... ) tbl

Does nothing and can be omitted.

Your query can be simplified to (although its not clear which columns you want in the SELECT clause):

SELECT se.*,
       s.subject_id,
       sces.study_event_id
FROM   v_study_event se
       CROSS JOIN v_subject s
       LEFT OUTER JOIN v_subject_current_event_status sces
       ON sces.study_event_id = se.study_event_id

If you want to add WHERE filters then either:

1. Add them to the end of the query as normal; or
2. If you want to filter v_subject_current_event_status before joining then add the filters to the ON clause of the join.

Answer 2

The problem has been fixed. It seems the first select * gets confused. I had to write out all columns I needed from the joined views.

The columns study_event_id needed to be specified to lefttable.study_event_id.

This query worked:

SELECT
    study_event_id
FROM
    (
        SELECT
            lefttable.study_event_id study_event_id
        FROM
            (
                SELECT
                    ea.*,
                    sbj.subject_id
                FROM
                    v_study_event ea,
                    v_subject     sbj
            ) lefttable
            LEFT OUTER JOIN (
                SELECT
                    *
                FROM
                    v_subject_current_event_status
            ) righttable ON righttable.study_event_id = lefttable.study_event_id
        
    ) tbl