Database view - Help with Statement

86
3
Jump to solution
10-02-2019 11:19 AM
Highlighted
by
Occasional Contributor III

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!!! 

Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
Esri Frequent Contributor

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.

--- George T.

View solution in original post

3 Replies
Highlighted
Esri Frequent Contributor

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.

--- George T.

View solution in original post

Highlighted
MVP Regular Contributor

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
Highlighted
by
Occasional Contributor III

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

Reply
0 Kudos