Database view - Help with Statement

[PanGIS]

Hi!

Very basic question. not for me....

I am trying to create a database view with this statement. (both from catalog and from toolbox)

CREATE VIEW readings_view
 AS SELECT (b.shortname,b.longname,r.timestamp,r.room,r.acmode,r.actemp)
 FROM #nameofFeatureClass# b, #nameOfTable# r
 WHERE b.shortname = r.room;

I get this error

There is already a relationship 1:M between the two. but I am trying to crate this view and publish it as I need to crate a symbology from related table. I read that creating a database view is a possible way but I cannot get it.

What do I do wrong?

I am trying to replicate what is in here: Example: Create database views in SQL Server using SQL—Help | ArcGIS Desktop

thanks!!! 

[George_Thompson · Accepted solution]

That link that you provided is for doing it in SQL Server Management Studio (SSMS) not in the ArcGIS Client.

Try this:

b.shortname,b.longname,r.timestamp,r.room,r.acmode,r.actemp)
 FROM #nameofFeatureClass# b, #nameOfTable# r
 WHERE b.shortname = r.room;

I do not think that our client will honor the "b." or "r" portion.

[Asrujit_SenGupta]

I do not think that our client will honor the "b." or "r" portion.

That should not be an issue.

Try the Syntax without the "CREATE VIEW readings_view AS" when trying to create view in ArcGIS as George mentioned.

SELECT b.shortname, b.longname, r.timestamp, r.room, r.acmode, r.actemp
FROM #nameofFeatureClass# b, #nameOfTable# r
WHERE b.shortname = r.room‍‍‍

[PanGIS]

George Thompson

Asrujit SenGupta

It works guys!

thank you!!!!

I added some new fields, also the shape so it will show the geometry, and the symbology works.

Another question if I may, I am sorry to stress out too much:

The query that works is this one:

SELECT b.objectid, b.globalid, b.shortname, b.longname, b.shape, r.timestamp, r.room, r.acmode, r.actemp
FROM BLKJ_Building b, Readings r
WHERE b.shortname = r.room

I would like to select the last time stamp only and a range. (the range ideally for a time slider)

for last time stamp I've tried this one, but doesn't work.

Any idea of what should be the correct one?

SELECT b.objectid, b.globalid, b.shortname, b.longname, b.floor, b.shape, MAX(r.timestamp), r.room, r.acmode, r.actemp
FROM Building b, Readings r
WHERE b.shortname = r.room