Estoy tratando de unir dos tablas de subselecciones, pero no parece funcionar. Recibo el error missing right parentheses o ORA-00903 Invalid Table Name. Soy bastante nuevo en Oracle y no sé exactamente dónde está el error. Las dos consultas funcionan sin la combinación, pero no funcionan cuando agrego la combinación.

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

¿Alguna idea de dónde está mi error o cómo podría escribir esto de una mejor manera para evitar el error?

¡Gracias de antemano!

1
Max 8 dic. 2020 a las 10:16

2 respuestas

La mejor respuesta

No puedes tener

INNER JOIN SELECT ...

Ese SELECT debería ser una subconsulta, p. Ej.

INNER JOIN (SELECT ...)

Algo como esto:

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
1
Littlefoot 8 dic. 2020 a las 07:21

Tienes SELECT * FROM. extra. Necesitas eliminar eso y todo funcionará bien.

...
...
    WHERE Station = 'Intensivstation I' AND REGEXP_LIKE(Next_Stat, '(Stat)') 
    GROUP BY Fallnr, Station
) t1
INNER JOIN 
--SELECT * FROM -- just remove this  
(
    SELECT Fallnr, MIN(Beginn) AS Wiederaufnahme FROM (
...
...
0
Popeye 8 dic. 2020 a las 07:22