9 Replies Latest reply on May 10, 2010 4:16 PM by user7182953

Error in a report after enabling case insensitive search in conn pool prop

700663 Jun 26, 2009 10:09 AM

Hi All,

I put the below code in connection pool's connection string to enable case insensitive search.
alter session set NLS_SORT=BINARY_CI
alter session set NLS_COMP=LINGUISTIC

After putting this code one of my report started giving the below error which was working fine otherwise.

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. NQODBC SQL_STATE: HY000 nQSError: 10058 A general error has occurred. nQSError: 16001 ODBC error state: S1000 code: 1791 message: OracleODBCOraORA-01791: not a SELECTed expression. nQSError: 16001 ODBC error state: S1000 code: 1791 message: OracleODBCOraORA-01791: not a SELECTed expression. nQSError: 16015 SQL statement execution failed. (HY000)

The report gives error for the below divide condition when I select a column from a different dimension (eg: region)
COUNT(DISTINCT RMA.RMA)/COUNT(DISTINCT User."User Name")

Can anyone please throw a light why this is happening.

1. Re: Error in a report after enabling case insensitive search in conn pool prop

gerardnico Jun 26, 2009 12:13 PM (in response to 700663)

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.
Like Show 0 Likes(0)

Actions
2. Re: Error in a report after enabling case insensitive search in conn pool prop

700663 Jun 30, 2009 6:24 AM (in response to gerardnico)

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.
Like Show 0 Likes(0)

Actions
3. Re: Error in a report after enabling case insensitive search in conn pool prop

SaichandVaranasi Jun 30, 2009 6:34 AM (in response to 700663)
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
Like Show 0 Likes(0)

Actions
4. Re: Error in a report after enabling case insensitive search in conn pool prop

700663 Jun 30, 2009 6:54 AM (in response to SaichandVaranasi)

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.
Like Show 0 Likes(0)

Actions
5. Re: Error in a report after enabling case insensitive search in conn pool prop

SaichandVaranasi Jun 30, 2009 7:07 AM (in response to 700663)

hi,

i don't know about that refer to this

http://www.orafaq.com/node/91
Like Show 0 Likes(0)

Actions
6. Re: Error in a report after enabling case insensitive search in conn pool prop

gerardnico Jun 30, 2009 8:48 AM (in response to 700663)
In the SQL that you gave, this condition is not good :
T1609.ATTRIB_39 'FV'
Where is the operator ? (equallity ?)
Like Show 0 Likes(0)

Actions
7. Re: Error in a report after enabling case insensitive search in conn pool prop

700663 Jun 30, 2009 10:29 AM (in response to gerardnico)

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.
Like Show 0 Likes(0)

Actions
8. Re: Error in a report after enabling case insensitive search in conn pool prop

gerardnico Jun 30, 2009 10:39 AM (in response to 700663)

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.
Like Show 0 Likes(0)

Actions
9. Re: Error in a report after enabling case insensitive search in conn pool prop

user7182953 May 10, 2010 4:16 PM (in response to gerardnico)

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.
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.