I have a Oracle pivot query that works with 19 values in the IN clause but when I add a 20th, it fails with an ORA-01427 error.
This works:
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;
This fails:
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;
I've verified that it is not a data issue and can get the query to run if I take out any one value in the IN clause
This works as well, leave '200.0' in and remove '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;
Am I going crazy (probably) or is there something I am missing? Thanks.
We'll need to see a more complete test case, because I can't reproduce that here
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>