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 ACCT_ID
FROM (SELECT ACCT_ID,
CUST_ID,
CUST_TO_ACCT_RELATIONSHIP_CD,
ROW_NUMBER ()
OVER (
PARTITION BY ACCT_ID
ORDER BY
TO_NUMBER (CUST_TO_ACCT_RELATIONSHIP_CD))
KEY
FROM OODSCIF.CUST_TO_ACCT_RELATIONSHIP_B C
WHERE APP_CD = ‘AFS’)
WHERE KEY = 1
Could you please tell me the alternative of these keywords that can be used in the Cross connection editor?
Also while executing the above keywords in the CrossConnection editor i am getting the error message - > Go to Helpetopicapath/topic/topic.htm for more information.
Please let me know the exact path of the help file.
Thanks,
Bharat Bhusan Singh
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-Query.
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 Connection Edior.
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
deptno,
ename,
sal,
ROW_NUMBER() OVER (PARTITION
BY deptno ORDER BY sal) MYRANK
FROM SMOKETEST_ORACLE11 (QUEST_DEV), QUEST_DEV.scott.emp
WHERE deptno = 30)
But when you add the where condition on the rank column to the parent query we try to process the where condition on the client side.
select
deptno as MyDeptno,
ename
as MyEname,
sal
as Salary
FROM ( SELECT deptno,
ename,
sal,
ROW_NUMBER() OVER (PARTITION
BY deptno ORDER BY sal) MYRANK
FROM SMOKETEST_ORACLE11 (QUEST_DEV), QUEST_DEV.scott.emp
WHERE deptno = 30)
WHERE
MYRANK < 3;
Try moving removing your parent query of the ranking query or move the where condition to be processin on the Oracle side.
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
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.
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_ID
ORDER BY
TO_NUMBER (CUST_TO_ACCT_RELATIONSHIP_CD))
KEY
FROM OODSCIF.CUST_TO_ACCT_RELATIONSHIP_B C
WHERE APP_CD = ‘AFS’) ADD_TABLE_ALIAS_HERE
WHERE KEY = 1
In the current version the query optimization parser expects derided tables to be aliased.
Stewart.