Finally, I have figured out the issue and it is resolved.
The view opt.best is a user view which gets builts at run time everytime a user tries to access the data from the view opt.best through a defined query .
So the query defined to build this view has case statement which compares some string values (values are mentioned in ALL CAPS in case statement) to populate the column BLEND_TYP_DSC so when I fire a query that is trying to access the view opt.best the view gets built at run time and in case of ANSI mode the case statement is not working fine, so I am getting incorrect values. (in case of TERDATA is has nothing to do with case so getting correct output)
Thank you so much Dave and dnoeth for your help in fixing this.
Glad to hear you got it resolved. Just to clarify, the problem was caused by case-sensitivity in one or more WHEN clauses?
Yes, the problem was caused by case-sensitivity in the case statement.
Thank you once again, I got to learn many other things from you apart from your help in fixing the issue.