OBIEE + Snowflake Database - Query error

MarcosSpínola

Hello.

I'm connecting OBIEE 12c (Product Version 12.2.1.3.0 (Build BIPS-20170820114118 64-bit) to a Snowflake database, using ODBC connection (3.5 ODBC). The connection information was entered in the ODBC file (Server, Database, Role, Warehouse and Username).

I created a connection pool and pointed to this new DSN I created.

I can reach the database and import Snowflake metadata do OBI using the admin tool. I can also see the data in snowflake tables using the "View Data" functionality in OBI Admin Tool, even though, before showing the data, OBI prompts a message:

But when I create a report on OBI for snowflake's subject area, it fails with the error:

Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)

State: HY000. Code: 16001. [nQSError: 16001] ODBC error state: 42000 code: 904 message: SQL compilation error: error line 9 at position 23 invalid identifier 'ROWNUM'. (HY000)

State: HY000. Code: 16014. [nQSError: 16014] SQL statement preparation failed. (HY000)

The SQL query OBI is issuing to the database is this (column and table names were replaced):

WITH 
SAWITH0 AS (select distinct T3696135.ACCOUNT_NUMBER as c1,
     T3696135.COLUMN1 as c2,
     T3696135.COLUMN2 as c3,
     T3696135.COLUMN3 as c4,
     T3696135.COLUMN4 as c5,
     T3696135.COLUMN5 as c6
from 
     TABLENAME )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7 from ( select 0 as c1,
     D1.c1 as c2,
     D1.c2 as c3,
     D1.c3 as c4,
     D1.c4 as c5,
     D1.c5 as c6,
     D1.c6 as c7
from 
     SAWITH0 D1
order by c6, c4, c3, c2, c7, c5 ) D1 where rownum <= 1000001

And if fails with this error message:

[nQSError: 16001] ODBC error state: 42000 code: 904 message: SQL compilation error: error line 19 at position 43
invalid identifier 'ROWNUM'.

Is there any way I can disable this "where rownum <= 1000001" at the end of the query? Also, is there anything I'm missing with the ODBC configuration?

User_KLYW8

Hi,I am also facing the same issue. Please let me know if your issue is fixed.

[Deleted User]

https://community.oracle.com/tech/apps-infra/discussion/comment/16839092#Comment_16839092

This thread is more than a year old. If you have a question please open a new thread and clearly and precisely state your issue including all pertinent information like precise version numbers (not "12c") etc.