Oracle数据透视表

我有一个Oracle pivot查询，可以在in子句中使用19个值，但是当我添加第20个值时，它会失败并出现ORA-01427错误。

这是可行的:

select * from (select serial_no, buyoff_user || chr(13) || to_char(buyoff_date) || chr(13) || serial_oper_status || chr(13) || NCR as UPD_BY, oper_no 
from db1.vw_status s where order_no = '123456')  
pivot (min(UPD_BY) 
for oper_no in ('10.0','20.0','30.0','40.0','50.0','60.0','70.0','80.0','90.0','100.0','110.0','120.0','130.0','140.0','150.0','160.0','170.0','180.0','190.0')) 
order by serial_no;

失败:

select * from (select serial_no, buyoff_user || chr(13) || to_char(buyoff_date) || chr(13) || serial_oper_status || chr(13) || NCR as UPD_BY, oper_no 
from db1.vw_status s where order_no = '123456')  
pivot (min(UPD_BY) 
for oper_no in ('10.0','20.0','30.0','40.0','50.0','60.0','70.0','80.0','90.0','100.0','110.0','120.0','130.0','140.0','150.0','160.0','170.0','180.0','190.0','200.0')) 
order by serial_no;

我已经验证这不是数据问题，并且如果我在in子句中取出任何一个值，都可以运行查询

这也可以，保留 “200.0” 并删除 “60.0”

select * from (select serial_no, buyoff_user || chr(13) || to_char(buyoff_date) || chr(13) || serial_oper_status || chr(13) || NCR as UPD_BY, oper_no 
from db1.vw_status s where order_no = '123456')  
pivot (min(UPD_BY) 
for oper_no in ('10.0','20.0','30.0','40.0','50.0','70.0','80.0','90.0','100.0','110.0','120.0','130.0','140.0','150.0','160.0','170.0','180.0','190.0','200.0')) 
order by serial_no;

我是疯了 (可能) 还是我缺少什么？谢谢。

我们需要看到一个更完整的测试用例，因为我不能在这里重现

SQL> with vw_status as
  2  ( select
  3      trunc(rownum/10) serial_no,
  4      'upd'||rownum upd_by,
  5      to_char(mod(rownum,20)+1)||'0.0' oper_no
  6    from dual
  7    connect by level <= 100
  8  )
  9  select * from (select serial_no, UPD_BY, oper_no
 10  from vw_status s )
 11  pivot (min(UPD_BY)
 12  for oper_no in ('10.0','20.0','30.0','40.0','50.0','60.0','70.0','80.0','90.0','100.0','110.0','120.0','130.0','140.0','150.0','16
0.0','170.0','180.0','190.0','200.0'))
 13  order by serial_no;

 SERIAL_NO '10.0'                                      '20.0'
---------- ------------------------------------------- -------------------------------------------
'30.0'                                      '40.0'
------------------------------------------- -------------------------------------------
'50.0'                                      '60.0'
------------------------------------------- -------------------------------------------
'70.0'                                      '80.0'
------------------------------------------- -------------------------------------------
'90.0'                                      '100.0'
------------------------------------------- -------------------------------------------
'110.0'                                     '120.0'
------------------------------------------- -------------------------------------------
'130.0'                                     '140.0'
------------------------------------------- -------------------------------------------
'150.0'                                     '160.0'
------------------------------------------- -------------------------------------------
'170.0'                                     '180.0'
------------------------------------------- -------------------------------------------
'190.0'                                     '200.0'
------------------------------------------- -------------------------------------------
         0                                             upd1
upd2                                        upd3
upd4                                        upd5
upd6                                        upd7
upd8                                        upd9






         1




upd10                                       upd11
upd12                                       upd13
upd14                                       upd15
upd16                                       upd17
upd18                                       upd19

         2 upd20                                       upd21
upd22                                       upd23
upd24                                       upd25
upd26                                       upd27
upd28                                       upd29






         3




upd30                                       upd31
upd32                                       upd33
upd34                                       upd35
upd36                                       upd37
upd38                                       upd39

         4 upd40                                       upd41
upd42                                       upd43
upd44                                       upd45
upd46                                       upd47
upd48                                       upd49






         5




upd50                                       upd51
upd52                                       upd53
upd54                                       upd55
upd56                                       upd57
upd58                                       upd59

         6 upd60                                       upd61
upd62                                       upd63
upd64                                       upd65
upd66                                       upd67
upd68                                       upd69






         7




upd70                                       upd71
upd72                                       upd73
upd74                                       upd75
upd76                                       upd77
upd78                                       upd79

         8 upd80                                       upd81
upd82                                       upd83
upd84                                       upd85
upd86                                       upd87
upd88                                       upd89






         9




upd90                                       upd91
upd92                                       upd93
upd94                                       upd95
upd96                                       upd97
upd98                                       upd99

        10 upd100











11 rows selected.

SQL>
SQL>