SQL and PL/SQL

Get “ORA-56901: non-constant expression is not allowed for pivot|unpivot values” ? Not a big deal, check a workaround!

Problem

Get “ORA-56901: non-constant expression is not allowed for pivot|unpivot values” while trying to execute SQL statement with pivot clause. Lets consider this tiny sample:

  • Here is the table:

 

  • SQL statement with PIVOT clause and constant expressions, which executes successfully:
  • SQL statement with PIVOT clause and dynamic expressions, which fails:
Solution
  • Workaround – just don’t use PIVOT at this point, simple group by clause will fullfill requirement:

 

  • Here is the .sql file with presented content to download.
Oracle DB version 11.2.0.4.0 

If this post was helpful, please visit an advertisement site! Thank You.


Advertisement

2 thoughts on “Get “ORA-56901: non-constant expression is not allowed for pivot|unpivot values” ? Not a big deal, check a workaround!

  1. but that’s not a solution for the required problem,if we have to use Decode,why we should go for PIVOT,Is there any workaround with PIVOT to resolve the Problem?

    1. Hi Pratik,
      exactly, thats just a workaround to fulfill a requirement to pivot data using non constant expression for pivoting axis. As it is a clear and legal limitation of PIVOT function, I just don’t want to cheat it. Please share if you discover how to achieve this in some way using PIVOT function. Thanks!
      Good Luck!

Leave a Reply

Your email address will not be published.