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

832
3
Jump to solution
07-01-2019 02:59 AM
robert_at_work
New Contributor III

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

Tags (3)
0 Kudos
1 Solution

Accepted Solutions
robert_at_work
New Contributor III

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

View solution in original post

3 Replies
CraigSwadner
Occasional Contributor

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

0 Kudos
robert_at_work
New Contributor III

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

Screenshot SQL developersql delevoper

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
0 Kudos
robert_at_work
New Contributor III

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