Home » SQL & PL/SQL » SQL & PL/SQL » Urgent-ORA-01445
Urgent-ORA-01445 [message #34446] Mon, 13 December 2004 18:32 Go to next message
Ankur
Messages: 22
Registered: August 2000
Junior Member
Hi All,
I am getting the following error when I try to execute the a select query with many joins
ORA-01445: cannot select ROWID from, or sample, a join view without a Key-preserved table.
Can some body tell me the reason for the error?

I am not selecting any rowid, or creating any view.

If I just rearrange the query with some left joins in the top, the query is working..
I basically want to know the reason for why it is happening….

Regards
Ankur

SELECT
count(*)
FROM TEMPREMIT TEMPREMIT
INNER JOIN PAYMENT PAYMENT
ON TEMPREMIT.PAYMENTID = PAYMENT.PAYMENTID
INNER JOIN CLAIM CLAIM
ON CLAIM.CLAIMID = TEMPREMIT.CLAIMID
INNER JOIN ENROLLKEYS ENROLLKEYS
ON CLAIM.ENROLLID = ENROLLKEYS.ENROLLID
INNER JOIN ELIGIBILITYORG ELIGIBILITYORG
ON ENROLLKEYS.ELIGIBLEORGID = ELIGIBILITYORG.ELIGIBLEORGID
INNER JOIN CLAIMDETAIL CLAIMDETAIL
ON TEMPREMIT.CLAIMID = CLAIMDETAIL.CLAIMID
AND TEMPREMIT.LINENUMBER = CLAIMDETAIL.CLAIMLINE
INNER JOIN CLAIMREMIT CLAIMREMIT
ON TEMPREMIT.CLAIMID = CLAIMREMIT.CLAIMID
AND TEMPREMIT.LINENUMBER = CLAIMREMIT.CLAIMLINE
LEFT JOIN MESSAGE MESSAGE
ON CLAIMREMIT.MSGNUMBER = MESSAGE.MESSAGEID
INNER JOIN ABF_STATIC_RA_EOB_LETTER_CODES ABFRACLAIMREMIT
ON CLAIMREMIT.MSGNUMBER = ABFRACLAIMREMIT.PK_ABF_STATIC_REASON_CODE
AND ABFRACLAIMREMIT.OTHER_DATA = 'CLAIMRA'
INNER JOIN CLAIMEDIT CLAIMEDIT
ON TEMPREMIT.CLAIMID = CLAIMEDIT.CLAIMID
AND TEMPREMIT.LINENUMBER = CLAIMEDIT.CLAIMLINE
INNER JOIN RULEREASON RULEREASON
ON CLAIMEDIT.REASON = RULEREASON.REASONID
INNER JOIN ABF_STATIC_RA_EOB_LETTER_CODES ABFRACLAIMEDIT
ON CLAIMEDIT.REASON = ABFRACLAIMEDIT.PK_ABF_STATIC_REASON_CODE
AND ABFRACLAIMEDIT.OTHER_DATA = 'EDITRA'
INNER JOIN CLAIMEOB CLAIMEOB
ON TEMPREMIT.CLAIMID = CLAIMEOB.CLAIMID
AND TEMPREMIT.LINENUMBER = CLAIMEOB.CLAIMLINE
LEFT JOIN MESSAGE MESSAGE2
ON CLAIMEOB.MSGNUMBER = MESSAGE2.MESSAGEID
INNER JOIN ABF_STATIC_RA_EOB_LETTER_CODES ABFRACLAIMEOB
ON CLAIMEOB.MSGNUMBER = ABFRACLAIMEOB.PK_ABF_STATIC_REASON_CODE
AND ABFRACLAIMEOB.OTHER_DATA = 'CLAIMEOB'
INNER JOIN CLAIMEDIT EOBCLAIMEDIT
ON TEMPREMIT.CLAIMID = EOBCLAIMEDIT.CLAIMID
AND TEMPREMIT.LINENUMBER = EOBCLAIMEDIT.CLAIMLINE
INNER JOIN RULEREASON EOBRULEREASON
ON EOBCLAIMEDIT.REASON = EOBRULEREASON.REASONID
INNER JOIN ABF_STATIC_RA_EOB_LETTER_CODES ABFRACLAIMEDITEOB
ON EOBCLAIMEDIT.REASON = ABFRACLAIMEDITEOB.PK_ABF_STATIC_REASON_CODE
AND ABFRACLAIMEDITEOB.OTHER_DATA = 'EDITEOB'
LEFT JOIN MEMBER MEMBER
ON MEMBER.MEMID = CLAIM.MEMID
LEFT JOIN ENTITY MEMBERENTITY
ON MEMBER.ENTITYID = MEMBERENTITY.ENTID
LEFT JOIN MEMBERRELATION MEMBERRELATION
ON MEMBER.MEMID = MEMBERRELATION.RELMEMID
LEFT JOIN MEMBER MEMBERRELATE
ON MEMBER.MEMID = MEMBERRELATE.MEMID
LEFT JOIN PROVIDER
ON TEMPREMIT.PROVID = PROVIDER.PROVID
LEFT JOIN ENTITY PROVIDERENTITY
ON PROVIDERENTITY.ENTID = PROVIDER.ENTITYID
LEFT JOIN ENROLLKEYS MEMENROLLKEYS
ON MEMBER.MEMID = MEMENROLLKEYS.MEMID
AND MEMENROLLKEYS.EFFDATE <= TEMPREMIT.DOSEND
AND MEMENROLLKEYS.TERMDATE >= TEMPREMIT.DOSSTART
AND MEMENROLLKEYS.SEGTYPE = 'INT'
LEFT JOIN ORGPOLICY MEMORGPOLICY
ON MEMENROLLKEYS.ORGPOLICYID = MEMORGPOLICY.ORGPOLICYID
LEFT JOIN ENROLLKEYS MEMRELENROLLKEYS
ON MEMBER.MEMID = MEMRELENROLLKEYS.MEMID
AND MEMRELENROLLKEYS.EFFDATE <= TEMPREMIT.DOSEND
AND MEMRELENROLLKEYS.TERMDATE >= TEMPREMIT.DOSSTART
AND MEMRELENROLLKEYS.SEGTYPE = 'INT'
LEFT JOIN ORGPOLICY MEMRELORGPOLICY
ON MEMRELENROLLKEYS.ORGPOLICYID = MEMRELORGPOLICY.ORGPOLICYID
LEFT JOIN PROGRAM PROGRAM
ON TEMPREMIT.PROGRAMID = PROGRAM.PROGRAMID
LEFT JOIN QFUND REMITFUND
ON TEMPREMIT.FUNDID = REMITFUND.FUNDID
LEFT JOIN QFUND PROGFUND
ON PROGRAM.FUNDID = PROGFUND.FUNDID
LEFT JOIN PAYCHECK PAYCHECK
ON TEMPREMIT.PAYMENTID = PAYCHECK.PAYMENTID
AND TEMPREMIT.FUNDID = PAYCHECK.FUNDID
AND TEMPREMIT.CHECKNUMBER = PAYCHECK.CHECKNBR
Re: Urgent-ORA-01445 [message #34454 is a reply to message #34446] Tue, 14 December 2004 08:43 Go to previous messageGo to next message
Edward Stoever
Messages: 58
Registered: August 2002
Member
Try breaking up the query into smaller pieces. It would take anyone a very long time to figure that one out. You are joining so many objects! It may work eventually, but it will be very slow, I am sure.

Edward
Re: Urgent-ORA-01445 [message #222859 is a reply to message #34446] Tue, 06 March 2007 13:59 Go to previous messageGo to next message
asadri
Messages: 1
Registered: March 2007
Location: IRVINE, CA
Junior Member
This is a known bug. You cannot join more than 6 times (7 joins) to the same key with the same name if your select statement has more than certain number of columns (not sure how many). Bug information below:

Bug 4369235 ANSI joins may fail with ORA-1445
This note gives a brief overview of bug 4369235.

Affects:
Product (Component)
Oracle Server (Rdbms)

Range of versions believed to be affected
Versions < 11

Versions confirmed as being affected
9.2.0.6
10.1.0.4
10.2.0.1

Platforms affected
Generic (all / most platforms affected)


Fixed:
This issue is fixed in
9.2.0.8 (Server Patch Set)
10.1.0.5 (Server Patch Set)
10.2.0.2 (Server Patch Set)
11g (Future version)


Symptoms: Related To:
Error May Occur
ORA-1445
ANSI Joins


Description
ORA-1445 can occur for ANSI join queries with large views/subqueries.

Workarounds:

1-Transform the ansi join queries manually.
2-Ristrict number of columns in your select statement.
3-restrict your joins to 6


Re: Urgent-ORA-01445 [message #327751 is a reply to message #34446] Tue, 17 June 2008 08:03 Go to previous messageGo to next message
glen.breivik@aibel.com
Messages: 1
Registered: June 2008
Junior Member
Old original posting. I had Excactly same error with a later oracle patch released after the mentioned fix.
I have oracle ver 10.2.0.3.0
I have a view in oracle DB that I can view entirely with oracle SQL developer 1.5.1 build 5440, but it can not export to csv unless reducing to a few colums. Logfile says ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table.
Solution for me was using PLSQL Developer. The file has more than 300.000 rows with about 20 columns, looks like a Oracle SQL developer limitation in my case?
Re: Urgent-ORA-01445 [message #357919 is a reply to message #327751] Fri, 07 November 2008 04:11 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

We are on version 10.2.0.4, however we still receive the same error.

No matter, even if we use PL/SQL develeoper or SQL Plus

Sharath
Re: Urgent-ORA-01445 [message #357921 is a reply to message #357919] Fri, 07 November 2008 04:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Post your query if you want any help at all. NOt a lot we can do without it.

I suspect that Metalink would be a good place to go with this problem.

Re: Urgent-ORA-01445 [message #357922 is a reply to message #357921] Fri, 07 November 2008 04:32 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Just wanted to update others that come to this forum that this problem exists on 10.2.0.4

Thanks,

Sharath
Re: Urgent-ORA-01445 [message #357923 is a reply to message #357922] Fri, 07 November 2008 04:34 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or at least that a problem that causes ORA-1445 exists in 10.2.0.4.

I don't think that either you or I are in a position to state that it's the same bug.
Previous Topic: Query based on the other query data
Next Topic: Row Wise in Column Wise
Goto Forum:
  


Current Time: Thu Mar 28 05:51:43 CDT 2024