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 ?
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.
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.
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
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.
