Toad World® Forums

TDP & Oracle ROWNUM in Heterogeneous Queries

Toad Data Point
#1

Does anyone know if ROWNUM can be used in a cross-connection query? We are using it to create an identity value but when placed in a ccq between Oracle and Teradata we are receiving an ODBC error where ROWNUM is being treated as if it was a value in Teradata despite it being used in portion of the query that is using the oracle connection string. It reads like a driver error, but not sure why it's looking at Teradata for ROWNUM.

Cross Query Database Error: ODBC data source returned an error: Column ROWNUM not found in DB1.TV0VTST3ICASN7QB8SQ3SEIEK7GJG.

#2

ROWNUM, unfortunately, cannot be used in TDP's cross-connection mode. Reason is that cross-connect queries are accomplished via mySQL on the back end. MySQL, to date, doesn't support a ROWNUM function or pseudo column.

As a work-around, I'm hopeful that doing something like this will help you out (trying to keep it to one SQL statement, rather than extra procedural code lines around your query):

    SELECT @rownum:=@rownum+1 rownum, t.*
    FROM (SELECT @rownum:=0) r, {{table}} t;

where {{table}} represents your table name.

#3

I'll give it a shot. Thanks for the suggestion.