Using Union All is limitation and hence the problem.

This SQL is build dynamically by front end and it has limitation wherein it is not supporting Union All (views, Stored procedure as well).