Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Equivalent keyword in Oracle for QUALIFY of Teradata

447396Jun 9 2009 — edited Jun 9 2009
Hi,

In teradata database we can write a query like below :

select sls_doc_id, sls_dlvr_doc_id, rank() over (partition by sls_dlvr_doc_id order by sls_dlvr_doc_id) rn
from cdp_analysis.v_sls_dlvr_doc_dtl
QUALIFY rn > 1;

If I want to write an equivalent thing in Oracle then I can write an equivalent like below :

select sls_doc_id, sls_dlvr_doc_id
from
(
select sls_doc_id, sls_dlvr_doc_id, rank() over (partition by sls_dlvr_doc_id order by sls_dlvr_doc_id) rn
from cdp_analysis.v_sls_dlvr_doc_dtl
)
where rn > 1;

I just want to know is any keyword exist in Oracle which is equivalent to QUALIFY of Teradata?

Regards,
Koushik

Comments

Kalpataru

Hi Soofi,

Is your source database and destination database version are same , check the both database version properly ?

Is your view have rowid column ?

If yes then remove the rowid column and try.

You can also check this threads for your reference.

http://www.orafaq.com/forum/t/119514/

ORA-02070: database TO_LINK does not support operator USERENV in this cont

SQL Error: ORA-02070: database does not support in this context

If your issue is not resolved then you can ask it in Oracle Support Forms forum for better help.

Andreas Weiden
Answer

If oyu use a "foreign" database other than Oracle, the concept of ROWID's is unknown.

You can overcome this by changing the Key-Mode of the block to "Non-Updateable" and mark your PK-column-item in the block by Setting the property "Primary Key" to "Yes"

Marked as Answer by Soofi · Sep 27 2020
Soofi

Thanks Andreas Weiden,

It worked for me. But what if my foreign table has no primary key.

Regards,

Soofi

Andreas Weiden

But what if my foreign table has no primary key.

Well, then you are out of luck (and you have a problem with the database-design).

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 7 2009
Added on Jun 9 2009
1 comment
5,802 views