从子选择中选择

Question

因此，下面的示例查询会导致一个错误(对于那些感兴趣的人，它: ORA-00904："BLEH"：无效标识符)

select bleh from (
            SELECT MIN(mtb.id) KEEP (DENSE_RANK FIRST ORDER BY mtb.id) "bleh"
              FROM mytable mtb
             WHERE mtb.filtered_field_one = 0
               and mtb.filtered_field_two = 0
          GROUP BY mtb.foreighn_id, mtb.id
);

如果我将其改为(注意，我将bleh替换为*)：

select * from (
            SELECT MIN(mtb.id) KEEP (DENSE_RANK FIRST ORDER BY mtb.id) "bleh"
              FROM mytable mtb
             WHERE mtb.filtered_field_one = 0
               and mtb.filtered_field_two = 0
          GROUP BY mtb.foreighn_id, mtb.id
);

我从第一个查询中得到的结果与我所期望的完全一样，它看起来像一个名为bleh的列，具有适当的值。

那么，为什么我不能按该列的名称(别名)进行选择呢？

Answer 1

当您用"双引号命名列时，数据库需要按原样调用它，同时也要使用双引号！所以试着这样做：

select "bleh" from (
        SELECT MIN(mtb.id) KEEP (DENSE_RANK FIRST ORDER BY mtb.id) "bleh"
          FROM mytable mtb
         WHERE mtb.filtered_field_one = 0
           and mtb.filtered_field_two = 0
      GROUP BY mtb.foreighn_id, mtb.id
); 

或者这样：

select bleh from (
        SELECT MIN(mtb.id) KEEP (DENSE_RANK FIRST ORDER BY mtb.id) bleh
          FROM mytable mtb
         WHERE mtb.filtered_field_one = 0
           and mtb.filtered_field_two = 0
      GROUP BY mtb.foreighn_id, mtb.id
); 

正如我所说的as is，我认为@Stawros提供的评论是一种宝贵的信息平静：

至于甲骨文中的列命名：

bleh = BLEH = Bleh
but 
"bleh" <> "BLEH" <> "Bleh"