Solved

Oracle - Error with "where" statement

Posted on 2009-05-11
9
699 Views
Last Modified: 2013-11-16
The following WHERE command produces error ORA-01084 when submitted in a subquery from an oracle database to another oracle database using a DBLINK.  This works fine when run within either database not using a DBLINK.

Please advise and Thanks in advance

WHERE    DOC.DIRECTION = 'I' 
AND      DOC.SUBMISSIONDATE between to_date('2009-04-29', 'YYYY-MM-DD') and to_date('2009-04-30', 'YYYY-MM-DD')  
and

Open in new window

0
Comment
Question by:pandkyon
  • 5
  • 4
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24358124
can you show more information about the query that does not work?
0
 

Author Comment

by:pandkyon
ID: 24358202
Here is the code:

Thanks!
	
SELECT   DOC.SUBMISSIONDATE
		, DOC.SENDERID
		, DOC.RECEIVERID
		, DOC.DIRECTION 
		, DIR.DIRECTIONNAME as TransDirection
		, DOC.TRANSACTIONSETID
		, TRN.TRANSACTIONNAME as TransName
		, DOC.NUMDOCS as NumSubmitted
		, DOC.NUMDOCSACCEPTED as NumAccepted 
		, DOC.NUMDOCSREJECTED as NumRejected
		, SENDER.PARTNERID as SenderPartnerID
		, SENDER.IDENTIFIER as SenderIdentifier
		, SENDER.NAME as SenderName
		, SENDER.DESCRIPTION as SenderDescription
		, SENDER.ISINTERNAL as SenderIsInternal
		, SENDER.DIRECTSUBMITTER as SenderDirSubmitter
		, SENDER.DIRECTSUBMITTERIDENTIFIER as SenderDirSubmitterID
		, RECEIVER.PARTNERID as ReceiverPartnerID
		, RECEIVER.IDENTIFIER as ReceiverIdentifier
		, RECEIVER.NAME as ReceiverName
		, RECEIVER.DESCRIPTION as ReceiverDescription
		, RECEIVER.ISINTERNAL as ReceiverIsInternal
		, RECEIVER.DIRECTSUBMITTER as ReceiverDirSubmitter
		, RECEIVER.DIRECTSUBMITTERIDENTIFIER as ReceiverDirSubmitterID		
		, ROU.ROUTECODE AS RouteCd
		, TAX.TAXID as ProvTaxID
		, USA.USAGEDESCRIPTION as UsageDesc
		, DAT.CALENDARDATE
		, DAT.CALENDARYEAR
		, DAT.CALENDARMONTH
		, DAT.CALENDARMONTHNAME
		, NPI.NPI as NationalProviderID
		,STATESYS.CLAIMSYSTEMBYSTATE as StateAndSystem
		,CLMSYS.CLAIMSYSTEM 
		,COBA.COBAID
 
		FROM     DOCUMENT837FACT@Repprod1.REPORTDB DOC 
		join     PARTNERDIM@Repprod1.REPORTDB SENDER		on DOC.SENDERID		=		SENDER.PARTNERID 
		join     PARTNERDIM@Repprod1.REPORTDB RECEIVER	on DOC.RECEIVERID	=		RECEIVER.PARTNERID 
		LEFT JOIN     ROUTECODEDIM@Repprod1.REPORTDB ROU 		ON DOC.ROUTECODEID	=		ROU.ROUTECODEID 
		LEFT JOIN     TAXIDDIM@Repprod1.REPORTDB TAX 			ON DOC.TAXIDID		=		TAX.TAXIDID
		JOIN     USAGEINDICATORDIM@Repprod1.REPORTDB USA ON DOC.USAGEINDICATORID	=	USA.USAGEINDICATORID 
		JOIN     DATEDIM@Repprod1.REPORTDB DAT 			ON DOC.SUBMISSIONDATE	=	DAT.CALENDARDATE 
		LEFT JOIN     NPIDIM@Repprod1.REPORTDB NPI 			ON DOC.NPIID			=	NPI.NPIID 
		JOIN 	 TRANSACTIONSETDIM@Repprod1.REPORTDB TRN ON DOC.TRANSACTIONSETID =   TRN.TRANSACTIONSETID
		JOIN	 DIRECTIONDIM@Repprod1.REPORTDB DIR		ON DOC.DIRECTION		=	DIR.DIRECTION
		LEFT JOIN	COBAIDDIM@Repprod1.REPORTDB COBA ON DOC.COBAIDID = COBA.COBAIDID
		LEFT JOIN CLAIMSYSTEMBYSTATEDIM@Repprod1.REPORTDB STATESYS ON DOC.CLAIMSYSTEMBYSTATEID = STATESYS.CLAIMSYSTEMBYSTATEID
		LEFT JOIN CLAIMSYSTEMDIM@Repprod1.REPORTDB CLMSYS ON DOC.CLAIMSYSTEMID = CLMSYS.CLAIMSYSTEMID
 
WHERE    
	     DOC.DIRECTION = 'I' 
AND      DOC.SUBMISSIONDATE between to_date('2009-04-29', 'YYYY-MM-DD') and to_date('2009-04-30', 'YYYY-MM-DD')  
and 	 DOC.USAGEINDICATORID = '0'

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24358369
2 things:
§ what is the data type of DOC.SUBMISSIONDATE ?
§ you might consider creating a (materialized) view of the data on the linked server, and just query that one... performance will be much better...
 
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:pandkyon
ID: 24358691
AngelIII

Thanks,

submissiondate is DATE datatype.  i don't know what a materialized view is
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24358739
0
 

Author Comment

by:pandkyon
ID: 24363445
Any idea why it's erroring out when using the where clause?

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24363486
my next check would be this:
ON DOC.SUBMISSIONDATE      =      DAT.CALENDARDATE

you said that DOC.SUBMISSIONDATE is datetime, so what about DAT.CALENDARDATE ?
0
 

Author Closing Comment

by:pandkyon
ID: 31580299
Points awarded for the assistance and pointing me in the right direction.

thanks
Answer:
ORA-00600: internal error code, arguments: [qerrmObnd1], [1084], [ORA-01084: invalid argument in OCI call
I checked the oracle support site and I can confirm that this is an Oracle bug Bug 4456478 - OERI:[qerrmObnd] from remote mapped queries
which has been fixed in Oracle 10.1.0.5 and 10.2.0.2
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24375784
Author Comments:
Points awarded for the assistance and pointing me in the right direction.

thanks
Answer:
ORA-00600: internal error code, arguments: [qerrmObnd1], [1084], [ORA-01084: invalid argument in OCI call
I checked the oracle support site and I can confirm that this is an Oracle bug Bug 4456478 - OERI:[qerrmObnd] from remote mapped queries
which has been fixed in Oracle 10.1.0.5 and 10.2.0.2



note that you did not initially post the ora-00600 initially, which would have made me to suggest the metalink/oracle support immediately...
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Read about achieving the basic levels of HRIS security in the workplace.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question