OAC generates invalid ROWNUM clause against Snowflake when using RPD subject area

Dennis Paredes

When running an analysis built on an RPD-based Data Model that connects to Snowflake, OAC incorrectly injects an Oracle-specific WHERE rownum <= 500001 clause into the generated SQL.Snowflake does not support ROWNUM → query fails immediately with:

SQL compilation error: error line X at position Y invalid identifier 'ROWNUM'

Example of the failing generated SQL:

SELECT ...FROM ...WHERE ... AND rownum <= 500001 -- ← This causes the error in Snowflake

Expected Behavior: OAC should generate Snowflake-compatible row-limiting syntax when the database type is Snowflake, for example:

FETCH FIRST 500001 ROWS ONLY

OR

LIMIT 500001

Any tips, known workarounds, or patches would be greatly appreciated to fix this error!

Dennis Paredes

Resolved by deleting existing connection pool and creating a new one as Snowflake DB.

SteveF-Oracle

Hi @Dennis Paredes ,

This may be difficult to resolve on a forum. Additional details of the RPD configuration, and review of the logs may be required.

A general question, if you create a connection in /dv against the same schema, then create a workbook against a dataset or a local subject area, does the same issue occur?

SteveF-Oracle

Glad you resolved it.