Oracle JOIN with subqueries - invalid table name

Question

I'm trying to join two tables from subselects, but it doesn't seem to work. I either get the error missing right parentheses or ORA-00903 Invalid Table Name. I'm quite new to Oracle and I don't quite know where exactly the error is. The two queries do work without the join, but they don't work when I'm adding the join.

SELECT * FROM (
    SELECT Fallnr, MIN(Ende) AS Entlassung, Station FROM (
        SELECT Fallnr, Station, LAG(Station, 1) OVER (ORDER BY Beginn) AS Prev_Stat, LEAD(Station, 1) OVER (ORDER BY Beginn) AS Next_Stat, Beginn, Ende FROM (  
            SELECT
                f.FALLNR AS Fallnr,
                vfs.GUELTIG_AB AS Beginn,
                vfs.GUELTIG_BIS AS Ende,
                o.OEBENENAME AS Station
            FROM
                FALL f 
                INNER JOIN V_FALL_STATION vfs ON vfs.FALLID = f.FALLID
                INNER JOIN ORGAEBENE o ON O.OEBENEID = vfs.OEBENEID 
            WHERE
                o.BS_PERSNR = 100000
            ORDER BY Beginn, Ende
        )
        WHERE REGEXP_LIKE(Station, '^Intensivstation I$|(Stat)')
    )
    WHERE Station = 'Intensivstation I' AND REGEXP_LIKE(Next_Stat, '(Stat)') 
    GROUP BY Fallnr, Station
) t1
INNER JOIN 
SELECT * FROM (
    SELECT Fallnr, MIN(Beginn) AS Wiederaufnahme FROM (
        SELECT Fallnr, Station, LAG(Station, 1) OVER (ORDER BY Beginn) AS Prev_Stat, LEAD(Station, 1) OVER (ORDER BY Beginn) AS Next_Stat, Beginn, Ende FROM (  
            SELECT
                f.FALLNR AS Fallnr,
                vfs.GUELTIG_AB AS Beginn,
                vfs.GUELTIG_BIS AS Ende,
                o.OEBENENAME AS Station
            FROM
                FALL f 
                INNER JOIN V_FALL_STATION vfs ON vfs.FALLID = f.FALLID
                INNER JOIN ORGAEBENE o ON O.OEBENEID = vfs.OEBENEID 
            WHERE
                o.BS_PERSNR = 100000
            ORDER BY Beginn, Ende
            )
            WHERE REGEXP_LIKE(Station, '^Intensivstation I$|(Stat)') 
        )
    WHERE Station = 'Intensivstation I' AND REGEXP_LIKE(Prev_Stat, '(Stat)') 
    GROUP BY Fallnr, Station
) t2 ON t2.FALLNR = t1.FALLNR

Any ideas where my mistake is or how I could write this in a better way to avoid the error?

Thanks in advance!

Answer 1

You can't have

INNER JOIN SELECT ...

That SELECT should be a subquery, e.g.

INNER JOIN (SELECT ...)

Something like this:

SELECT *
  FROM (  SELECT Fallnr, MIN (Ende) AS Entlassung, Station
            FROM (SELECT Fallnr,
                         Station,
                         LAG (Station, 1) OVER (ORDER BY Beginn) AS Prev_Stat,
                         LEAD (Station, 1) OVER (ORDER BY Beginn) AS Next_Stat,
                         Beginn,
                         Ende
                    FROM (  SELECT f.FALLNR AS Fallnr,
                                   vfs.GUELTIG_AB AS Beginn,
                                   vfs.GUELTIG_BIS AS Ende,
                                   o.OEBENENAME AS Station
                              FROM FALL f
                                   INNER JOIN V_FALL_STATION vfs
                                      ON vfs.FALLID = f.FALLID
                                   INNER JOIN ORGAEBENE o
                                      ON O.OEBENEID = vfs.OEBENEID
                             WHERE o.BS_PERSNR = 100000
                          ORDER BY Beginn, Ende)
                   WHERE REGEXP_LIKE (Station, '^Intensivstation I$|(Stat)'))
           WHERE     Station = 'Intensivstation I'
                 AND REGEXP_LIKE (Next_Stat, '(Stat)')
        GROUP BY Fallnr, Station) t1
       INNER JOIN
       (SELECT *
          FROM (  SELECT Fallnr, MIN (Beginn) AS Wiederaufnahme
                    FROM (SELECT Fallnr,
                                 Station,
                                 LAG (Station, 1) OVER (ORDER BY Beginn)
                                    AS Prev_Stat,
                                 LEAD (Station, 1) OVER (ORDER BY Beginn)
                                    AS Next_Stat,
                                 Beginn,
                                 Ende
                            FROM (  SELECT f.FALLNR AS Fallnr,
                                           vfs.GUELTIG_AB AS Beginn,
                                           vfs.GUELTIG_BIS AS Ende,
                                           o.OEBENENAME AS Station
                                      FROM FALL f
                                           INNER JOIN V_FALL_STATION vfs
                                              ON vfs.FALLID = f.FALLID
                                           INNER JOIN ORGAEBENE o
                                              ON O.OEBENEID = vfs.OEBENEID
                                     WHERE o.BS_PERSNR = 100000
                                  ORDER BY Beginn, Ende)
                           WHERE REGEXP_LIKE (Station,
                                              '^Intensivstation I$|(Stat)'))
                   WHERE     Station = 'Intensivstation I'
                         AND REGEXP_LIKE (Prev_Stat, '(Stat)')
                GROUP BY Fallnr, Station)) t2
          ON t2.FALLNR = t1.FALLNR