topic Database view - Help with Statement in Data Management Questions

Database view - Help with Statement

PanGIS — Wed, 02 Oct 2019 18:19:08 GMT

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

Re: Database view - Help with Statement

George_Thompson — Wed, 02 Oct 2019 19:13:53 GMT

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.

Re: Database view - Help with Statement

Asrujit_SenGupta — Sat, 11 Dec 2021 15:17:50 GMT

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

Re: Database view - Help with Statement

PanGIS — Thu, 03 Oct 2019 10:48:06 GMT

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