Category: MetaSolv System

Oracle – LISTAGG

I needed to collapse multiple rows into a single row — the circuits within a diversity set are stored within the ds_dvrsty_set_circuit table as individual rows & the ds_dvrsty_set_id links the multiple rows. What I wanted was a set ID, set name, and the list of circuits within the set.

To accomplish this, I found LISTAGG which is a little bit like STUFF in MSSQL. This query produces a single row for each diversity set that contains the set ID, the set name, and a comma delimited list of set members.

SELECT
     ds_dvrsty_set_circuit.ds_dvrsty_set_id,
     (select ds_dvrsty_set.ds_dvrsty_set_nm from ds_dvrsty_set where ds_dvrsty_set_id = ds_dvrsty_set_circuit.ds_dvrsty_set_id) as set_name,
     LISTAGG(ds_dvrsty_set_circuit.circuit_design_id,  ',') WITHIN GROUP(ORDER BY ds_dvrsty_set_circuit.ds_dvrsty_set_id) AS member_circuits
FROM
     ds_dvrsty_set_circuit
     left outer join ds_dvrsty_set on ds_dvrsty_set.ds_dvrsty_set_id = ds_dvrsty_set_circuit.DS_DVRSTY_SET_ID
WHERE
     ds_dvrsty_set_circuit.ds_dvrsty_set_id in (select distinct ds_dvrsty_set_id from ds_dvrsty_set_circuit where circuit_design_id in (14445678, 5078901) )
AND
     ds_dvrsty_set.ds_dvrsty_set_nm like '43%'
GROUP BY
     ds_dvrsty_set_circuit.ds_dvrsty_set_id
ORDER BY
     ds_dvrsty_set_circuit.ds_dvrsty_set_id;

Voila — exactly what I needed. If the searched circuit design IDs appear in more than one set, there is a new row for each set ID.

MetaSolv: Programmatically creating diversity sets

We wanted to be able to bulk-load diversity sets into MSS. While the documentation has several additional fields in the CIRCUIT table that claim to be updated when a circuit is a member of a diversity set (SPECIAL_ROUTING_CODE_IO_FAC, SPECIAL_ROUTING_CODE_SECLOC, and SPECIAL_ROUTING_CODE_TYPE), I’ve found these fields to remain NULL when sets are created through the MSS GUI.

To create a diversity set, an API call will be made. The following values would be used for each interaction with the API.

Parameter Description Required? Field Table Action
strDiversitySetName User-defined name of the diversity set (32 character limit) YES DS_DVRSTY_SET_NM asap.ds_dvrsty_set INSERT
charDiversitySetTypeCode Type of diversity to maintain for the set. Valid values are ‘2’, ‘3’, and ‘4’ YES DS_DVRSTY_SET_TYPE_CD asap.ds_dvrsty_set INSERT
n/a The user account that made the most recent change to the row. This will always be g9953576. n/a LAST_MODIFIED_USER_ID asap.ds_dvrsty_set INSERT
n/a The timestamp when the last change to the row occurred n/a LAST_MODIFIED_DATE asap.ds_dvrsty_set INSERT

Adding circuits to the newly created diversity set requires the Oracle-generated sequence, DS_DVRSTY_SET_ID, which is created by the previous call. To add circuits to the diversity set, an API call will be made. The following values would be used for each interaction with the API.

Parameter Description Required? Field Table Action
iDiversitySetID Diversity Set ID from asap.ds_dvrsty_set YES DS_DVRSTY_SET_ID ds_dvrsty_set_circuit INSERT
iDiversitySetSequence Circuit sequence number within diversity set YES DS_DVRSTY_SET_SEQ ds_dvrsty_set_circuit INSERT
iCircuitDesignID Unique identifier for circuit YES CIRCUIT_DESIGN_ID ds_dvrsty_set_circuit INSERT
charPrimaryRouteIndicator Indicates if the circuit is the primary route circuit. Valid values are ‘Y’ and ‘N’ YES DS_PRIMARY_ROUTE_IND ds_dvrsty_set_circuit INSERT
charExcludeIndicator Indicates whether this circuit within the Diversity Set is excluded from the diversity requirement YES DS_EXCLUDE_IND ds_dvrsty_set_circuit INSERT
n/a The user account that made the most recent change to the row. This will always be g9953576. n/a LAST_MODIFIED_USERID ds_dvrsty_set_circuit INSERT
n/a The timestamp when the last change to the row occurred n/a LAST_MODIFIED_DATE ds_dvrsty_set_circuit INSERT
n/a Indicates circuit belongs to a diversity set. This value will always be ‘Y’ n/a DS_DIVERSITY_IND circuit UPDATE