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
Solved! Go to Solution.
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
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
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
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