Oracle两层子查询报错问题及解决

业务场景

比如我有一个电信的套餐，在某个月申请对套餐进行调整，这个调整会在下个月进行生效，数据库中会涉及到两个表：套餐变更表、月份表。我们从套餐变更表中查询出变更的月份，然后通过月份表找到下一个月份作为生效月份，语句大概如下：

select modify_month,
(select month from (select month from t_month t where t.month > a.modify_month order by month) where rownum=1) as effect_month
from t_combo_his a;

但执行这个语句oracle会报一个错误：a.modify_month: invalid identifier。

原因是oracle有些版本不支持嵌套的两层子查询这种写法，

select (select b.字段 from 
(select 字段 from 表1 where 表1.列 = 表2.列) b
) c
 from 表2 ;

可以通过如下语句来判断当前使用的oracle版本是否支持这种写法：

select (select * from (select a.dummy from dual a where a.dummy = dual.dummy)) from dual;

解决方法

对于上面所说的这个问题，解决方式是使用oracle的Keep函数，比如上面的那个场景，可以改成如下sql。

select modify_month,
(select min(month) keep(dense_rank first order by month) from t_month t where t.month > a.modify_month) as effect_month
from t_combo_his a;