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?