Is additional SQL syntax necessary to account for a field with a domain/coded values? The 2019 Stack Overflow Developer Survey Results Are InHow to select a number of rows based on current sort orderDeleting first two letters from string field usimng Python Parser of ArcGIS Field Calculator?Coded Value Domain is showing, but not Description?Feature class with attribute pick lists for values, without a Coded Value Domain?Coded Geodatabase Domain Values behaving oddly with special characters in the description fieldIs it possible to alter field display precision whilst using a coded domain in ArcGIS 10.2?Creating coded values with iteration from a fieldst_geometry syntax for Oracle SqlSQL Condition Statement in Select By Attributes queryWhat is the syntax for adding a query to a spatial view from a related table in ArcGIS?
! Package inputenc Error: Unicode character (U+2061) (inputenc) not set up for use with LaTeX
Match Roman Numerals
Does HR tell a hiring manager about salary negotiations?
Why are there uneven bright areas in this photo of black hole?
What do I do when my TA workload is more than expected?
Why doesn't shell automatically fix "useless use of cat"?
How to bridge a gap between two countertops?
Why does the nucleus not repel itself?
Why do many countries not offer a fixed rate 30 year mortgage?
Is an up-to-date browser secure on an out-of-date OS?
How can I add encounters in the Lost Mine of Phandelver campaign without giving PCs too much XP?
Loose spokes after only a few rides
How to translate "being like"?
Worn-tile Scrabble
"as much details as you can remember"
Button changing its text & action. Good or terrible?
What does Linus Torvalds mean when he says that Git "never ever" tracks a file?
Can a rogue use sneak attack with weapons that have the thrown property even if they are not thrown?
Compute the product of 3 dictionaries and concatenate keys and values
Mathematics of imaging the black hole
Table Fragmentation
Can we generate random numbers using irrational numbers like π and e?
What could be the right powersource for 15 seconds lifespan disposable giant chainsaw?
Why doesn't mkfifo with a mode of 1755 grant read permissions and sticky bit to the user?
Is additional SQL syntax necessary to account for a field with a domain/coded values?
The 2019 Stack Overflow Developer Survey Results Are InHow to select a number of rows based on current sort orderDeleting first two letters from string field usimng Python Parser of ArcGIS Field Calculator?Coded Value Domain is showing, but not Description?Feature class with attribute pick lists for values, without a Coded Value Domain?Coded Geodatabase Domain Values behaving oddly with special characters in the description fieldIs it possible to alter field display precision whilst using a coded domain in ArcGIS 10.2?Creating coded values with iteration from a fieldst_geometry syntax for Oracle SqlSQL Condition Statement in Select By Attributes queryWhat is the syntax for adding a query to a spatial view from a related table in ArcGIS?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I'm attempting to create a spatial view. The following statement returns an empty view, even though there are "Flushed" records in the MAINT_ACTIVITY field.
SELECT h.OBJECTID, h.FACILITYID, h.SHAPE, r.Hydrant_ID, r.MAINT_ACTIVITY, r.MaintDate, r.GlobalID
FROM wHydrant h
JOIN wHydrant_Maint r
ON h.FACILITYID = r.Hydrant_ID
WHERE r.MAINT_ACTIVITY = 'Flushed'
If I remove the last WHERE clause, the query will return a view of all my hydrants. If I change the WHERE clause to query another field, it will return correct results. I'm only having issues with this specific MAINT_ACTIVITY field and the only major difference is that this field has a domain with coded values. However, the coded values match the descriptions. So, Flushed is the code and Flushed is the description. Is additional syntax still necessary? Why doesn't the above statement work?
*Update--When I query other fields in the Hydrant_Maint table, all the MAINT_ACTIVITY field results return as NULL, even though there are entries in the original table.
arcgis-desktop sql spatial-view
asked Apr 5 at 20:53
NadarNadar
114
add a comment |
I'm attempting to create a spatial view. The following statement returns an empty view, even though there are "Flushed" records in the MAINT_ACTIVITY field.
SELECT h.OBJECTID, h.FACILITYID, h.SHAPE, r.Hydrant_ID, r.MAINT_ACTIVITY, r.MaintDate, r.GlobalID
FROM wHydrant h
JOIN wHydrant_Maint r
ON h.FACILITYID = r.Hydrant_ID
WHERE r.MAINT_ACTIVITY = 'Flushed'
If I remove the last WHERE clause, the query will return a view of all my hydrants. If I change the WHERE clause to query another field, it will return correct results. I'm only having issues with this specific MAINT_ACTIVITY field and the only major difference is that this field has a domain with coded values. However, the coded values match the descriptions. So, Flushed is the code and Flushed is the description. Is additional syntax still necessary? Why doesn't the above statement work?
*Update--When I query other fields in the Hydrant_Maint table, all the MAINT_ACTIVITY field results return as NULL, even though there are entries in the original table.
arcgis-desktop sql spatial-view
asked Apr 5 at 20:53
NadarNadar
114
It's not totally clear from your post but if you have all NULLs in the wHydrantMaint columns after the join, this indicates a problem with your join. Are h.FACILITYID and r.Hydrant_ID both the same data type?
– Andy
2 days ago
No, only the MAINT_ACTIVITY field results are null. All other fields in the wHydrant_Maint table return the correct entries. However to answer your question, yes, the join fields are both text types.
– Nadar
2 days ago
add a comment |
I'm attempting to create a spatial view. The following statement returns an empty view, even though there are "Flushed" records in the MAINT_ACTIVITY field.
SELECT h.OBJECTID, h.FACILITYID, h.SHAPE, r.Hydrant_ID, r.MAINT_ACTIVITY, r.MaintDate, r.GlobalID
FROM wHydrant h
JOIN wHydrant_Maint r
ON h.FACILITYID = r.Hydrant_ID
WHERE r.MAINT_ACTIVITY = 'Flushed'
If I remove the last WHERE clause, the query will return a view of all my hydrants. If I change the WHERE clause to query another field, it will return correct results. I'm only having issues with this specific MAINT_ACTIVITY field and the only major difference is that this field has a domain with coded values. However, the coded values match the descriptions. So, Flushed is the code and Flushed is the description. Is additional syntax still necessary? Why doesn't the above statement work?
*Update--When I query other fields in the Hydrant_Maint table, all the MAINT_ACTIVITY field results return as NULL, even though there are entries in the original table.
arcgis-desktop sql spatial-view
asked Apr 5 at 20:53
NadarNadar
114
I'm attempting to create a spatial view. The following statement returns an empty view, even though there are "Flushed" records in the MAINT_ACTIVITY field.
SELECT h.OBJECTID, h.FACILITYID, h.SHAPE, r.Hydrant_ID, r.MAINT_ACTIVITY, r.MaintDate, r.GlobalID
FROM wHydrant h
JOIN wHydrant_Maint r
ON h.FACILITYID = r.Hydrant_ID
WHERE r.MAINT_ACTIVITY = 'Flushed'
If I remove the last WHERE clause, the query will return a view of all my hydrants. If I change the WHERE clause to query another field, it will return correct results. I'm only having issues with this specific MAINT_ACTIVITY field and the only major difference is that this field has a domain with coded values. However, the coded values match the descriptions. So, Flushed is the code and Flushed is the description. Is additional syntax still necessary? Why doesn't the above statement work?
*Update--When I query other fields in the Hydrant_Maint table, all the MAINT_ACTIVITY field results return as NULL, even though there are entries in the original table.
arcgis-desktop sql spatial-view
arcgis-desktop sql spatial-view
asked Apr 5 at 20:53
NadarNadar
114
asked Apr 5 at 20:53
NadarNadar
114
edited Apr 8 at 18:39
Nadar
asked Apr 5 at 20:53
NadarNadar
114
asked Apr 5 at 20:53
NadarNadar
114
asked Apr 5 at 20:53
NadarNadar
114
114
It's not totally clear from your post but if you have all NULLs in the wHydrantMaint columns after the join, this indicates a problem with your join. Are h.FACILITYID and r.Hydrant_ID both the same data type?
– Andy
2 days ago
No, only the MAINT_ACTIVITY field results are null. All other fields in the wHydrant_Maint table return the correct entries. However to answer your question, yes, the join fields are both text types.
– Nadar
2 days ago
add a comment |
It's not totally clear from your post but if you have all NULLs in the wHydrantMaint columns after the join, this indicates a problem with your join. Are h.FACILITYID and r.Hydrant_ID both the same data type?
– Andy
2 days ago
No, only the MAINT_ACTIVITY field results are null. All other fields in the wHydrant_Maint table return the correct entries. However to answer your question, yes, the join fields are both text types.
– Nadar
2 days ago
It's not totally clear from your post but if you have all NULLs in the wHydrantMaint columns after the join, this indicates a problem with your join. Are h.FACILITYID and r.Hydrant_ID both the same data type?
– Andy
2 days ago
It's not totally clear from your post but if you have all NULLs in the wHydrantMaint columns after the join, this indicates a problem with your join. Are h.FACILITYID and r.Hydrant_ID both the same data type?
– Andy
2 days ago
No, only the MAINT_ACTIVITY field results are null. All other fields in the wHydrant_Maint table return the correct entries. However to answer your question, yes, the join fields are both text types.
– Nadar
2 days ago
No, only the MAINT_ACTIVITY field results are null. All other fields in the wHydrant_Maint table return the correct entries. However to answer your question, yes, the join fields are both text types.
– Nadar
2 days ago
add a comment |
1 Answer
1
active
oldest
votes
The SQL query in my question was actually correct. Upon viewing the base table in SSMS, I saw that all the MAINT_ACTIVITY entries were indeed NULL. Upon looking at the Views version of the table, I could see all my entries. So, because the table was registered as versioned, I just needed to change the query to search the view table, not the base table. The following returned the spatial view I was looking for:
SELECT h.OBJECTID, h.FACILITYID, h.SHAPE, r.Hydrant_ID, r.MAINT_ACTIVITY,
r.MaintDate, r.GlobalID
FROM wHydrant h
JOIN wHydrant_Maint_evw r
ON h.FACILITYID = r.Hydrant_ID
WHERE (r.MaintDate >= '2014' AND r.MaintDate <= '2015') AND r.MAINT_ACTIVITY = 'Flushed'
answered 2 days ago
NadarNadar
114
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f317946%2fis-additional-sql-syntax-necessary-to-account-for-a-field-with-a-domain-coded-va%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The SQL query in my question was actually correct. Upon viewing the base table in SSMS, I saw that all the MAINT_ACTIVITY entries were indeed NULL. Upon looking at the Views version of the table, I could see all my entries. So, because the table was registered as versioned, I just needed to change the query to search the view table, not the base table. The following returned the spatial view I was looking for:
SELECT h.OBJECTID, h.FACILITYID, h.SHAPE, r.Hydrant_ID, r.MAINT_ACTIVITY,
r.MaintDate, r.GlobalID
FROM wHydrant h
JOIN wHydrant_Maint_evw r
ON h.FACILITYID = r.Hydrant_ID
WHERE (r.MaintDate >= '2014' AND r.MaintDate <= '2015') AND r.MAINT_ACTIVITY = 'Flushed'
answered 2 days ago
NadarNadar
114
add a comment |
The SQL query in my question was actually correct. Upon viewing the base table in SSMS, I saw that all the MAINT_ACTIVITY entries were indeed NULL. Upon looking at the Views version of the table, I could see all my entries. So, because the table was registered as versioned, I just needed to change the query to search the view table, not the base table. The following returned the spatial view I was looking for:
SELECT h.OBJECTID, h.FACILITYID, h.SHAPE, r.Hydrant_ID, r.MAINT_ACTIVITY,
r.MaintDate, r.GlobalID
FROM wHydrant h
JOIN wHydrant_Maint_evw r
ON h.FACILITYID = r.Hydrant_ID
WHERE (r.MaintDate >= '2014' AND r.MaintDate <= '2015') AND r.MAINT_ACTIVITY = 'Flushed'
answered 2 days ago
NadarNadar
114
add a comment |
The SQL query in my question was actually correct. Upon viewing the base table in SSMS, I saw that all the MAINT_ACTIVITY entries were indeed NULL. Upon looking at the Views version of the table, I could see all my entries. So, because the table was registered as versioned, I just needed to change the query to search the view table, not the base table. The following returned the spatial view I was looking for:
SELECT h.OBJECTID, h.FACILITYID, h.SHAPE, r.Hydrant_ID, r.MAINT_ACTIVITY,
r.MaintDate, r.GlobalID
FROM wHydrant h
JOIN wHydrant_Maint_evw r
ON h.FACILITYID = r.Hydrant_ID
WHERE (r.MaintDate >= '2014' AND r.MaintDate <= '2015') AND r.MAINT_ACTIVITY = 'Flushed'
answered 2 days ago
NadarNadar
114
The SQL query in my question was actually correct. Upon viewing the base table in SSMS, I saw that all the MAINT_ACTIVITY entries were indeed NULL. Upon looking at the Views version of the table, I could see all my entries. So, because the table was registered as versioned, I just needed to change the query to search the view table, not the base table. The following returned the spatial view I was looking for:
SELECT h.OBJECTID, h.FACILITYID, h.SHAPE, r.Hydrant_ID, r.MAINT_ACTIVITY,
r.MaintDate, r.GlobalID
FROM wHydrant h
JOIN wHydrant_Maint_evw r
ON h.FACILITYID = r.Hydrant_ID
WHERE (r.MaintDate >= '2014' AND r.MaintDate <= '2015') AND r.MAINT_ACTIVITY = 'Flushed'
answered 2 days ago
NadarNadar
114
answered 2 days ago
NadarNadar
114
answered 2 days ago
NadarNadar
114
answered 2 days ago
NadarNadar
114
114
add a comment |
add a comment |
Thanks for contributing an answer to Geographic Information Systems Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f317946%2fis-additional-sql-syntax-necessary-to-account-for-a-field-with-a-domain-coded-va%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
It's not totally clear from your post but if you have all NULLs in the wHydrantMaint columns after the join, this indicates a problem with your join. Are h.FACILITYID and r.Hydrant_ID both the same data type?
– Andy
2 days ago
No, only the MAINT_ACTIVITY field results are null. All other fields in the wHydrant_Maint table return the correct entries. However to answer your question, yes, the join fields are both text types.
– Nadar
2 days ago