Solved

Oracle - Error with "where" statement

Posted on 2009-05-11
9
703 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 143

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 143

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:pandkyon
ID: 24358691
AngelIII

Thanks,

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

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 143

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 143

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ERP application comparison 1 35
Sql Server group by 10 44
Loops and updating in SQL Query 9 54
Currency in SQL? 2 31
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Salesforce.com is a cloud-based customer relationship management (CRM) system. In this article, you will learn how to add and map custom lead and contact fields to your Salesforce instance.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

829 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