I am trying to make a complex label and need help with the SQL statement

[sbraddo]

I would like to make a label that stacks up to 4 values for a feature. I am making the label using Topobase 2010 Admin tool. In the SQL statement below my goal is to get two values from table EL_Fuse and two values from Table EL_SWITCH when the records match my features FID to their attribute FID_Structure.

I am not trained in SQL writing but have come close with the following:

select concat_list(CURSOR(
select b.GC_DEVICEN||'\n'
||c.GC_DEVICEN||'\n'
from EL_SWITCHGEAR a, EL_FUSE b, EL_SWITCH c
where a.FID = b.FID_STRUCTURE
and b.FID_STRUCTURE = c.FID_STRUCTURE
and a.FID = $id
))myCursor from dual


This results in:
MA229\nMA227\nMA228\nMA227\nMA229\nMA226\nMA228\nMA226\n
1 rows selected.

What I want is:

MA226
MA227
MA228
MA229

MA226 and ma227 come from table EL_Switch and MA228 and MA229 Come from table EL_Fuse.

Any SQL experts out there that can correct my SQL so I get the results I want???

[sbraddo]

I kept hacking on this and came up with a solution:

select concat_list(CURSOR(
select concat_list(CURSOR(
select b.GC_DEVICEN||'\n'

from EL_SWITCHGEAR a, EL_FUSE b
where a.FID = b.FID_STRUCTURE

and a.FID = $id
))myCursor from dual
union

select concat_list(CURSOR(
select c.GC_DEVICEN||'\n'

from EL_SWITCHGEAR a, EL_switch c
where a.FID = c.FID_STRUCTURE

and a.FID = $id
))myCursor from dual
))myCursor from dual

which returns these results:
MA227\nMA226\nMA229\nMA228\n
1 rows selected.

Then when you style the label make sure the text type is text not Mtext. Only regular text recognizes the \n for next line.

[sbraddo]

After working with a real programmer we altered the code to the following:

SELECT REPLACE(WM_CONCAT(GC_DEVICEN||GC_DEVICE_TYPE ||'\n'),',',null) FROM
(SELECT FID_STRUCTURE, GC_DEVICEN, GC_DEVICE_TYPE FROM EL_FUSE
UNION
SELECT FID_STRUCTURE, GC_DEVICEN, GC_DEVICE_TYPE FROM EL_SWITCH)
GROUP BY FID_STRUCTURE
HAVING FID_STRUCTURE = $id

Because we are using a an older version of Oracle we had to use WM_CONCAT instead of LISTAGG. If you can use LISTAGG you do not need the REMOVE statement to get rid of the commas.