Sdetable * Special Where Clause

[CarlosRiano]

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

[VinceAngelo]

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

[CarlosRiano]

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?

[VinceAngelo]

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