Underlying DBMS error [ORA-00907: missing right parenthesis]

3923
4
Jump to solution
07-06-2013 04:07 AM
IshanKaushik
New Contributor
Hi,

I am tring to query SDE Geodatabase with following query to find duplicate elments in a feature class

 IQueryFilter subiqf = new QueryFilter();  subiqf.setWhereClause(fieldName+" In (SELECT "+fieldName+" FROM "+className.getName()+"GROUP BY "+fieldName+" HAVING  Count(*)>1 )");  IFeatureCursor subresultCursor = className.search(subiqf, false);  IFeature subif = subresultCursor.nextFeature();



This qery works fine when i try for Personal geodatabase but when i try it on SDE it produces following error--

Error: AutomationException: Underlying DBMS error [ORA-00907: missing right parenthesis
] [Data.Block]
Sample failed.  Exiting...
AutomationException: 0x80041538 - Underlying DBMS error [ORA-00907: missing right parenthesis] [Data.Block] in 'esriDataSourcesGDB.SdeWorkspace.1'

and points to this line ---

IFeature subif = subresultCursor.nextFeature();


Is there something i am missing for quring SDE geodatabase else why is it only working for PGBD and not SDE ?
0 Kudos
1 Solution

Accepted Solutions
RichardFairhurst
MVP Honored Contributor
The parentheses should be correct.  I think you are missing a space before the words GROUP BY and that the underlying SDE database parsing is not as forgiving as the parsing done by the personal geodatabase Microsoft Jet database engine.

subiqf.setWhereClause(fieldName+" In (SELECT "+fieldName+" FROM "+className.getName()+" GROUP BY "+fieldName+" HAVING Count(*)>1 )");

If that isn't it you need to share exactly what database underlies your SDE and look up the particular SQL syntax required for this kind of selection associated with that database.  Not all SQL is exactly alike across all databases and similar behaviors can differ in seemingly minor, but nonetheless important ways.

View solution in original post

4 Replies
MarcoBoeringa
MVP Regular Contributor
I think you have the last parenthesis placed wrong:

subiqf.setWhereClause(fieldName+" In (SELECT "+fieldName+" FROM "+className.getName()+"GROUP BY "+fieldName+" HAVING
Count(*)>1 )");

should be:

subiqf.setWhereClause(fieldName+" In (SELECT "+fieldName+" FROM "+className.getName()+"GROUP BY "+fieldName+" HAVING
Count(*)>1 ))";
0 Kudos
RichardFairhurst
MVP Honored Contributor
The parentheses should be correct.  I think you are missing a space before the words GROUP BY and that the underlying SDE database parsing is not as forgiving as the parsing done by the personal geodatabase Microsoft Jet database engine.

subiqf.setWhereClause(fieldName+" In (SELECT "+fieldName+" FROM "+className.getName()+" GROUP BY "+fieldName+" HAVING Count(*)>1 )");

If that isn't it you need to share exactly what database underlies your SDE and look up the particular SQL syntax required for this kind of selection associated with that database.  Not all SQL is exactly alike across all databases and similar behaviors can differ in seemingly minor, but nonetheless important ways.

View solution in original post

SaravananRaj
New Contributor

Hi Richard,

I too get the same situation. Here i uses "Inner Join", but it is not enabled as a function in add query layer in arcgis pro.

query:

select w.*, r.Total_plots from FGDABEDITZ40.SECTORTPSS AS w
Inner JOIN
(SELECT PLOTINTERNALID,SECTORTPSSNUMBER,COUNT(SECTORTPSSNUMBER) AS Total_plots FROM FGDABEDITZ40.PLOT GROUP BY SECTORTPSSNUMBER) AS r
ON w.SECTORTPSSNUMBER=r.SECTORTPSSNUMBER

0 Kudos
MarcoBoeringa
MVP Regular Contributor
The parentheses should be correct.


You're right, my mistake in the first post, the parentheses are correct.

I think you are missing a space before the words GROUP BY and that the underlying SDE database parsing is not as forgiving as the parsing done by the personal geodatabase Microsoft Jet database engine.


The missing space is good catch. I doubt Oracle would accept it, and it is amazing the Jet engine does... I usually insert a debug line after such dynamically created SQL queries, helps in catching these errors by reviewing the statement before processing.
0 Kudos