topic Re: create spatial view from one layer and two tables in Data Management Questions

create spatial view from one layer and two tables

AshakaThakore — Mon, 12 Dec 2011 04:04:19 GMT

I have a featurelayer(feature class -myfeatclass)
I have two tables mytab1 and mytab2
I want to create a view using myfeatclass that has some columns of mytab1 and some of mytab2

so i wroet this -

sdetable -o create_view -T MySpatView -t myfeatclass,mytab1,mytab2 -c myfeatclass.shape,mytab1.col1,mytab2.col2 -a shape,col1,col2 -u myuserid -p mypwd -w "myfeatclass.col1=mytab1.col1 and myfeatclass.col2=mytab2.col2"

This was giving syntax error.

So ii created oracle view using sql create myview as select a.* ,b.* from mytab1 a ,mytab2 b

Then i registered that view using sdetable create_view

Then I tried to create view using myfeatclass and myview
but its giving identifier is too long error !!!!!
does anybody has successfully created spatial view from one feature class and more than 1 table

thanks a lot

Re: create spatial view from one layer and two tables

VinceAngelo — Mon, 12 Dec 2011 10:14:00 GMT

I do this all the time, but you have to keep a few key facts in mind:

1) If you have native geometry (GEOMETRY/SDO_GEOMETRY/ST_GEOGRAPHY), you should
use SQL to construct your view, and register the resulting layer with 'sdelayer' (create_view
is only for SDEBINARY/SDELOB layers).

2) If you intend to use the resulting layer in ArcGIS, you *MUST* include an OBJECTID column
in your column list (it must map to SE_INT32_TYPE, be NOT NULL, and contain DISTINCT,
positive, non-zero, and repeatable values).

3) 'sdetable -o create_view' will use table aliases (-t "mytab1 a, mytab2 b"), but you cannot
alias the geometry column (it won't know what type to use)

4) Beware of polynomial expansion with one-to-many joins and multiple tables

You didn't say what version and service pack of ArcSDE you're using, or even which Oracle
release is under it, so it's hard to reproduce your issue. An "identifier too long" message
is generated if a column name exceeds 30 characters -- this has nothing to do with Esri
software (it's an Oracle limitation).

-V

Re: create spatial view from one layer and two tables

AshakaThakore — Mon, 12 Dec 2011 11:32:05 GMT

I am not using oracle spatial layers. I am using SDE Feature-layers which has shape column

I have Arcsde 9.3.1 I will check the service packs...

I cannot create a view-layer with more than one tables. It does not allow me to put more than ONE CONDITION in the -w(WHERE Clause) of sdetable command!!!

It gives me syntax error.

i.e. if i do this it gives error
-w "myfeatclass.col1=mytab1.col1 and myfeatclass.col2=mytab2.col2"

if I do this - (Without and clause ) it does not give error and it successfully creates a view...
-w "myfeatclass.col1=mytab1.col1"

I Tried putting in where clause as && but still it does not work.... that WHERE clause is failing

please help

Re: create spatial view from one layer and two tables

VinceAngelo — Mon, 12 Dec 2011 13:14:11 GMT

There isn't anything I can do to help. This syntax has always worked for me, so it likely has
something to do with your configuration. Make sure you have SP6 with the 3-4 post-SP6
patches and that your Oracle release is patched in conformance with the minimum supported
configuration, and if it still doesn't work, contact Tech Support for assistance.

- V

Re: create spatial view from one layer and two tables

AshakaThakore — Mon, 12 Dec 2011 13:19:39 GMT

Did "putting in two or more where clauses in -w" ever work for you? Thanks a lot

Re: create spatial view from one layer and two tables

VinceAngelo — Mon, 12 Dec 2011 15:15:10 GMT

It has always worked. At 9.1, 9.2, 9.3.1, and 10.0. It's rare that I *don't* have more than
two constraints, and it has never not worked.

- V

Re: create spatial view from one layer and two tables

AshakaThakore — Mon, 12 Dec 2011 15:21:49 GMT

Can you please give me example of how did you put more than one where clause
did you separate the where clause by comma or "And " or "&&"

thanks again

Re: create spatial view from one layer and two tables

VinceAngelo — Mon, 12 Dec 2011 16:18:59 GMT

I only use "and" in my SQL code:

sdetable -o create_view -T MySpatView ^
-t "myfeatclass,mytab1 a,mytab2 b" ^
-c myfeatclass.objectid,a.col1,b.col2,myfeatclass.shape ^
-w "myfeatclass.col1 = a.col1 and myfeatclass.col2 = b.col2" ^
-u myuserid -p ...

[Caret ("^") is the Windows line continuation character; it's backslash ("\") in Unix]

Once you start using ST_GEOMETRY you can use standard "JOIN" clauses via SQL, then
register the result once the view is stable.

- V

Re: create spatial view from one layer and two tables

AshakaThakore — Mon, 12 Dec 2011 16:22:19 GMT

Thanks Vince, I got it working by creating an oracle view using pl/sql create view myview as select a.*, b.* from mytab1 a , mytab2 b where a.col1=b.col1

An then i did a

sdetable -o create_view -T MySDEView -t mylayer,myview ............ -w mylayer.col1=myview.col1 //Without Quotes
And it worked...

Thanks a lot for your advice