Hi, I was trying to use ROW_NUMBER() and RANK() keyword in Cross Connection Editor. But its throwing some error while executing these keywords in Cross-Connection editor.Following is my query: SELECT CUST_ID AS PARTY_ID, CUST_TO_ACCT_RELATIONSHIP_CD AS CUST_TO_ACCT_RELATIONSHIP_CD, ACCT_ID AS AC…

Why do you want to use Cross-query for this? Your query doesn’t seem to use more than the one Oracle datasource. You are correct that Cross-query doesn’t seem to understand how to pass through the whole query to Oracle. But unless you need to join a different datasource you do not need to use Cross-…

This is only one part of whole query. I have 2 DBs that are joining to get the data. (1) Oracle (2) Greenplum Also, in Oracle itself some of data are coming from different schemas. So , I need to use the cross connection Editor. Please let me know how i can use ROW_NUMBER() and RANK() in Cross C…

You can join across different schemas and databases if they are on the same server without using a cross connection query. Just use From databaseName.SchemaName.Tablename AliasName

The DBs are not in same server. Is there any other workaround?

KEY might be a reserved word. Try putting the colunn alias name KEY inside of double quotes (i.e. “KEY”). In both your subquery and outer query.

The problem seems to be that when you put a where condition on the subquery we are not pushing the subquery to Oracle. For example, if I execute this query the whole SQL is pushed through to Oracle and the ROW_NUMMBER and Ranking function is processed by Oracle, which is what we want. SELECT dept…

I realize that to filter by the rank column you need to use the subquery. I have entered QAT-4588 to investigate the issue.

Hi Debbie, Any updates on this issue. Thanks, Bharat

Hi Bharat, For current GA, the workaround is to add sub-query alias to the query: SELECT CUST_ID AS PARTY_ID, CUST_TO_ACCT_RELATIONSHIP_CD AS CUST_TO_ACCT_RELATIONSHIP_CD, ACCT_ID AS ACCT_ID FROM (SELECT ACCT_ID, CUST_ID, CUST_TO_ACCT_RELATIONSHIP_CD, ROW_NUMBER () OVER ( PARTITION BY ACCT…

ROW_NUMBER() and RANK() keyword is not working in Cross Connection Editor.

Toad Data Point & Toad Intelligence Central

Debbie_Peabody January 5, 2015, 6:42pm 10

This issue was entered as QAT-4588. They have found the bug and fixed. The best I can do is see if I can get in an upcoming Beta build.

Topic		Replies	Views
TDP & Oracle ROWNUM in Heterogeneous Queries Toad Data Point & Toad Intelligence Central	2	938	February 24, 2020
Cross Connection Query running ... and running and running Toad Data Point & Toad Intelligence Central	5	882	March 29, 2012
Cross Connection Query Producing Different Toad Data Point & Toad Intelligence Central	11	1666	December 5, 2014
Running Cross Connection Query in an Automation Script Toad Data Point & Toad Intelligence Central	1	1103	November 20, 2014
Using Oracle Functions in Cross Connect Query Toad Data Point & Toad Intelligence Central	3	763	December 24, 2014

ROW_NUMBER() and RANK() keyword is not working in Cross Connection Editor.

Related topics