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.
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.
I'll give it a shot. Thanks for the suggestion.