nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Trouver plus de publications taguées avec

Accepted answers

George Thompson

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.

All comments

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

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.

<SPAN class="keyword token">SELECT</SPAN> <SPAN class="number token">b</SPAN><SPAN class="punctuation token">.</SPAN>shortname<SPAN class="punctuation token">,</SPAN> <SPAN class="number token">b</SPAN><SPAN class="punctuation token">.</SPAN>longname<SPAN class="punctuation token">,</SPAN> r<SPAN class="punctuation token">.</SPAN><SPAN class="keyword token">timestamp</SPAN><SPAN class="punctuation token">,</SPAN> r<SPAN class="punctuation token">.</SPAN>room<SPAN class="punctuation token">,</SPAN> r<SPAN class="punctuation token">.</SPAN>acmode<SPAN class="punctuation token">,</SPAN> r<SPAN class="punctuation token">.</SPAN>actemp
<SPAN class="keyword token">FROM</SPAN> <SPAN class="comment token">#nameofFeatureClass# b, #nameOfTable# r</SPAN>
<SPAN class="keyword token">WHERE</SPAN> <SPAN class="number token">b</SPAN><SPAN class="punctuation token">.</SPAN>shortname <SPAN class="operator token">=</SPAN> r<SPAN class="punctuation token">.</SPAN>room<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

George Thompson

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.

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.