topic Re: Underlying DBMS error [ORA-00907: missing right parenthesis] in ArcObjects SDK Questions

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

IshanKaushik — Sat, 06 Jul 2013 11:07:44 GMT

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 ?

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

MarcoBoeringa — Sat, 06 Jul 2013 12:41:05 GMT

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

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

RichardFairhurst — Sat, 06 Jul 2013 14:11:27 GMT

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.

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

MarcoBoeringa — Sun, 07 Jul 2013 07:31:12 GMT

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.

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

SaravananRaj — Mon, 24 Jun 2019 04:32:50 GMT

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