nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

IshanKaushik

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 ?

Find more posts tagged with

Accepted answers

RichardFairhurst

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.

All comments

SaravananRaj

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

MarcoBoeringa

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.

RichardFairhurst

MarcoBoeringa

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 ))";

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.