sql - ORA-00933: SQL 命令未在表别名上正确结束

Question

有问题让这个工作。每个查询都单独工作，但是当我尝试为每个子查询设置别名以使用 Join 时，我收到错误：

SQL 命令未正确结束。任何建议将不胜感激。

Select C.* 
From 
    (
        (
            Select 
                a.*,
                Row_Number() Over(Partition By referral_id Order By start_date,line) as rn 
            from hcclsc.referral_Bed_Day a
        ) A
        Inner Join (
            Select
                a.referral_id,
                max(rn) as vn
            From (
                Select
                    referral_id,
                    line,
                    bed_day_type_id,
                    start_date,
                    end_date,
                    Row_Number() Over(Partition by referral_id Order By start_date, line) as rn
                From HCCLSC.referral_Bed_Day 
            ) a
            Group by referral_id
        ) B
        On A.referral_ID = B.referral_id and a.rn = b.vn
    ) C

标签： sqloracle

Answer 1

You're joining A to B, but then not selecting a anything from those joined subqueries.

This part:

Select C.* 
From 
    (
        (

would need to specify columns to select from the join; if you want all of them from both subqueries then:

Select C.* 
From 
    (
        Select *
        From
            (

but then that extra level of subquery isn't really adding anything, and you can remove the C level:

Select * 
From 
    (
        Select 
            a.*,
            Row_Number() Over(Partition By referral_id Order By start_date,line) as rn 
        from hcclsc.referral_Bed_Day a
    ) A
    Inner Join (
        Select
            a.referral_id,
            max(rn) as vn
        From (
            Select
                referral_id,
                line,
                bed_day_type_id,
                start_date,
                end_date,
                Row_Number() Over(Partition by referral_id Order By start_date, line) as rn
            From HCCLSC.referral_Bed_Day 
        ) a
        Group by referral_id
    ) B
    On A.referral_ID = B.referral_id and a.rn = b.vn