topic Oracle View - two columns remain empty in ArcMap (but not in SQL Developer) in Geodatabase Questions

Oracle View - two columns remain empty in ArcMap (but not in SQL Developer)

robert_at_work — Mon, 01 Jul 2019 09:59:39 GMT

Hi there,

I have created a view in Oracle 11.2.. Its a view across three tables. I have contracts in one table and people related to the contracts in a second table (1:n). I need the people's names in a field next to the contract details (for export purposes only). Therefor I created a view using LISTAGG to concatenate all people's names related to a contract.

Works like a charm when viewed with SQL Developer. Unfortunately the column with the people’s names remains empty when viewed in ArcCatalog or opened in ArcMap. Its a table without any geometry. Any idea why just that single column remains empty? I casted the column type nvarchar2(500) - same data type and length as the column in the original table (which of course is displayed in ArcMap).

ArcMap 10.5.1, Win 10.

many thanks, Rob

Re: Oracle View - two columns remain empty in ArcMap (but not in SQL Developer)

CraigSwadner — Mon, 01 Jul 2019 12:17:41 GMT

Attach the View code…

Craig Swadner (GIS Coordinator)

City of Cape Coral

1015 Cultural Park Blvd.

Cape Coral, Fl 33990

Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net

Re: Oracle View - two columns remain empty in ArcMap (but not in SQL Developer)

robert_at_work — Sun, 12 Dec 2021 08:40:55 GMT

Oracle View Code - see below. I also tried a different seperator but it doesnt change the result.

Screenshot SQL developer

create or replace view vmg_v_dbv_vertrag_eig as
select 
objectid,
globalid,
dokument_nr, 
status, 
beschreibung, 
vertragsbeginn, 
vertragsende, 
entschaedigungsende,
geltung,
grundbuch,
typ,
art,
gemeinde,
gemeinde_aktuell,
bfs_nr,
parzelle_nr,
berechtigte_1,
berechtigte_2,
berechtigte_3,
berechtigte_4,
ersteller_1,
ersteller_2,
ersteller_3,
ersteller_4,
trasse_elemente,
spannung_1,
spannung_2,
spannung_3,
spannung_4,
archivbox,
cast(listagg(nachname || '; ' || vorname || ' / ') within group(order by nachname) as nvarchar2(500)) as eigentuemer_name, 
cast(listagg(plz || '; ' || wohnort || ' / ' ) within group(order by nachname) as nvarchar2(500)) as eigentuemer_plz_ort 
from
(
select 
v.objectid,
v.globalid,
v.dokument_nr, 
v.status, 
v.beschreibung, 
v.vertragsbeginn, 
v.vertragsende, 
v.entschaedigungsende,
v.geltung,
v.grundbuch,
v.typ,
v.art,
v.gemeinde,
v.gemeinde_aktuell,
v.bfs_nr,
v.parzelle_nr,
v.berechtigte_1,
v.berechtigte_2,
v.berechtigte_3,
v.berechtigte_4,
v.ersteller_1,
v.ersteller_2,
v.ersteller_3,
v.ersteller_4,
v.trasse_elemente,
v.spannung_1,
v.spannung_2,
v.spannung_3,
v.spannung_4,
v.archivbox,
ve.vertrag_ref, 
ve.eigentuemer_ref,  
e.nachname, 
e.vorname, 
e.plz,
e.wohnort

from vmg_dbv_vertrag v left join vmgr_vertrag_eigentuemer ve on v.globalid = ve.vertrag_ref
left join vmg_eigentuemer e on ve.eigentuemer_ref = e.globalid
--where v.dokument_nr = 'DBV013981'
--order by v.dokument_nr
)
group by 
objectid,
globalid,
dokument_nr, 
status, 
beschreibung, 
vertragsbeginn, 
vertragsende, 
entschaedigungsende,
geltung,
grundbuch,
typ,
art,
gemeinde,
gemeinde_aktuell,
bfs_nr,
parzelle_nr,
berechtigte_1,
berechtigte_2,
berechtigte_3,
berechtigte_4,
ersteller_1,
ersteller_2,
ersteller_3,
ersteller_4,
trasse_elemente,
spannung_1,
spannung_2,
spannung_3,
spannung_4,
archivbox;s‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Oracle View - two columns remain empty in ArcMap (but not in SQL Developer)

robert_at_work — Mon, 15 Jul 2019 11:35:48 GMT

Hi there,

I could solve the problem by doing a cast with "to_char". The LISTAGG command looks like that now:

cast(listagg(to_char(nachname) || '; ' || to_char(vorname) , ' / ') within group(order by nachname) as varchar2(4000)) as eigentuemer_name,‍

Further details can be found on my blog.

Rob