I got this error today with OBIEE 220.127.116.11.0. Obviously, all my columns were in the select list. I found a few blogs and articles with people asking for help for that bug but none of them provided/found a solution. Here is mine.
[nQSError: 42039] Columns in BY clause of REPORT totalling function must be in select list. (HY000)
The error is thrown when sorting one specific column (a measure) of a Table with 14 columns ((5 measures, 7 dimensions columns including 2 hierarchies). All the others columns could be sorted without problem except one.
Uncheck “Report-Based Total (when applicable)” in the aggregation rule of your measure. That’s it !
It worked for me !