Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Chris.

Asked: March 04, 2021 - 7:27 pm UTC

Last updated: March 08, 2021 - 2:19 pm UTC

Version: 12c

Viewed 100+ times

You Asked

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.

and we said...

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>


Rating

  (1 rating)

Comments

Problem solved

Chris Fisher, March 08, 2021 - 12:32 pm UTC

Thank you for your reply. The example you created was perfect and proved that the issue is not a limitation of the pivot table. Thanks to that, I created a work around and am able to get the query to work. The issue is data related, just not on the item I am querying. Thanks again.
Chris Saxon
March 08, 2021 - 2:19 pm UTC

Glad we could help

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.