SQL Developer

Check your log, retrieve your query :
http://gerardnico.com/wiki/dat/obiee/bi_server/log/obiee_query_performed

Copy/paste it in Toad of Sql developper and you will see a little bit more why.
And or give us the generated query.

Below is the SQL which is throwing error in OBIEE. It was working fine when I tried running it in toad:

select T1609.ATTRIB_05 as c1,
count(distinct T1609.ATTRIB_42) as c2,
TRUNC(T1159.FSCL_WEEK_START_DT) as c3
from
WC_DAY_D T1159 /* RMA_RECEIVED_DT(WC_DAY_D) */ ,
WC_RMA_D T571,
WC_FV_FA_D T1609,
WC_FV_FA_F T1679
where ( T571.ROW_WID = T1679.RMA_WID and T1159.ROW_WID = T1679.RMA_RECEIVED_WID and T1609.ROW_WID = T1679.FV_FA_WID and T1609.ATTRIB_39 <> 'FV' and (T571.STATUS_CD in ('2nd FA', '2nd FA Review', 'Closed')) and TRUNC(T1609.TODO_ACTL_END_DT) is not null and TRUNC(T1159.FSCL_WEEK_START_DT) between TIMESTAMP '2009-03-22 00:00:00' and TIMESTAMP '2009-04-12 00:00:00' )
group by T1609.ATTRIB_05, TRUNC(T1159.FSCL_WEEK_START_DT)
order by c3

-------------------- Query Status: Query Failed: [nQSError: 16001] ODBC error state: S1000 code: 1791 message: [Oracle][ODBC][Ora]ORA-01791: not a SELECTed expression.
[nQSError: 16001] ODBC error state: S1000 code: 1791 message: [Oracle][ODBC][Ora]ORA-01791: not a SELECTed expression.
[nQSError: 16015] SQL statement execution failed.

hi ,

Check the Query your getting

OracleODBCOraORA-01791: not a SELECTed expression.

In your query u r using the

  order by c3

Remove the ORDER BY item from the SELECT list and retry the statement. In the back then you will get different results and try the same from Answers too..


Thanks,
Saichand.V

This query is working fine in Toad. Also this SQL works fine if I remove below code from connection pool's connection string (used to enable case insensitive search):
alter session set NLS_SORT=BINARY_CI
alter session set NLS_COMP=LINGUISTIC

This is something to do with the above code.

hi,

i don't know about that refer to this

http://www.orafaq.com/node/91

In the SQL that you gave, this condition is not good :

T1609.ATTRIB_39  'FV'

Where is the operator ? (equallity ?)

Somehow the operator is not getting shown in the previous post (it is not equal to)

This SQL is working fine when I am running it in Toad. As I said previously,the same report works perfectly fine when I remove the parameters from Connection String.

Have you set also the same parameter sessions in Toad ?
To go further, you must retrieve the same problem in Oracle.
Compare your session parameters in Toad with the session parameters of OBIEE.

Then when you have the same error, work on your statement to find which part of it fired it.

I have similar issue - Did you find a correct reason or some solution here?

My query gives error as -
ORA-01791: not a SELECTed expression
when session settings are changed to make data case-insensitive.
cmmd1:='ALTER SESSION SET NLS_SORT=BINARY_CI';
cmmd2:='ALTER SESSION SET NLS_COMP=LINGUISTIC';
This is seen on both 10.2 and 11.1.0.7 releases.

When the above settings are removed then the query runs correctly.

SELECT DISTINCT C.ClientName, B.BillDesc, max(B.FacCode) FacCode, C.ClientNumber, Sum(B.BillAmt) AS SumOfBillAmt
FROM CVOClients C INNER JOIN CVOClientBills B ON C.ClientNumber = B.ClientNumber
WHERE B.ExportDate is null
GROUP BY C.ClientName, B.BillDesc, C.ClientNumber
ORDER BY C.ClientName

Why is it that the "order by" and "distinct" clause combination when nls_comp, and nls_sort is modified is something oracle cannot process.

Reasoning, help is appreciated. Thanks.
Nee H.