topic Re: Sdetable * Special Where Clause in Data Management Questions

Sdetable * Special Where Clause

CarlosRiano — Wed, 19 May 2010 15:37:37 GMT

Dear friends,

I am doing a View in SDE whit SQL. Usually all where clauses are similar, but this time I have one different and I couldn't write it properly. The table has some columns including CATEGORY and DATE. The idea is extract information from the max DATE for two Categories.

In Access, the SQL code is: SELECT * from [msc_mob] where date = (select max (date) from (select category, date from [msc_mob] where category = "Infantry")).

But when I write the where clause into ArcSDE it shows and error: Incorrect syntax near ')'. CAn any of you help me to check the code or give em a hand with a new code?!

The SDEtable command is the next:

sdetable -o create_view
-T TEST_VW
-t GISADMIN.DRCGWG_VV_GPS, gisadmin.MSC_MOB_COB
-c GISADMIN.DRCGWG_VV_GPS.Shape,
gisadmin.MSC_MOB_COB.ObjectID,
gisadmin.MSC_MOB_COB.CODE,
gisadmin.MSC_MOB_COB.LOCATION,
gisadmin.MSC_MOB_COB.BRIGADE,
gisadmin.MSC_MOB_COB.Category,
-w "GISADMIN.DRCGWG_VV_GPS.code = gisadmin.MSC_MOB_COB.code AND GISADMIN.DRCGWG_VV_GPS.usability=0 AND (select max(date) from (select gisadmin.MSC_MOB_COB.category, date from gisadmin.MSC_MOB_COB where gisadmin.MSC_MOB_COB.category='Infantry'))"
-D XXXX -u XXX-p XXXXXX -s XXXX-i XXXX

Re: Sdetable * Special Where Clause

VinceAngelo — Wed, 19 May 2010 16:49:57 GMT

Maybe it's an artifact of the posting environment, but as I read it, your WHERE clause
isn't legal SQL -- did you mean "AND date = (...subselect...)"?. Even if it is legal, you
might be running into a utility parse buffer issue with the long WHERE constraint.

It's not strictly supported, but you can generally add exotic options to the view after it's
created (as long as it doesn't result in unsupported columns or polynomial expansion
of the registered rowid column)-- but the SQL still has to pass muster with the database.

- V

Re: Sdetable * Special Where Clause

CarlosRiano — Thu, 20 May 2010 07:14:25 GMT

I haven´t find any solution. The clause is running in a SQL program but it is not under SDE.

Vangelo thank you with your answer, do you have any Idea for resolving this problem.. any other Code under sdetable?

Re: Sdetable * Special Where Clause

VinceAngelo — Thu, 20 May 2010 10:33:23 GMT

If it doesn't work on the command line, remove the final AND clause to let 'sdetable' succeed,
and then apply that clause via ALTER VIEW from a SQL client (like SQLDeveloper) later.

- V