Tag: metasolv

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

 

Oracle – Adding Fixed String To Column Data

I had a request to drop data from a SQL query into an Excel spreadsheet — the initial request had a column that included different text depending on row data. Since Box::Spout deals well with huge volumes of data (I write millions of rows in some reports), I use it pretty exclusively. Writing data to each column individually means I’ve got to retain a list of column names, and I would rather not do that. I can dump the column names into a header row then dump the entire database row into the next spreadsheet row. This works since a 1:1 correlation between database rows and Excel rows. I’ll go through after the fact and update a specific cell based on data in other cells, but I don’t want to insert a column.

Luckily, there’s an easy way to add a placeholder column to my database output:

SELECT DISTINCT MAX(DLR1.ISSUE_NBR) MAX_ISSUE_NBR, DLR.ISSUE_NBR, ‘MatchIndicator’ as “MatchIndicator”, DLR.EQUIPMENT_ID, EQ.EQUIPMENT_ID, C.CIRCUIT_DESIGN_ID, C.EXCHANGE_CARRIER_CIRCUIT_ID, C.STATUS, C.RATE_CODE, C.SERVICE_TYPE_CATEGORY, C.SERVICE_TYPE_CODE, NL.LOCATION_ID, NL.CLLI_CODE, DLR.LOCATION, DLR.BLOCK_IND

MetaSolv Solutions Service Request Search Status Buttons

I’m working on a project to automate the creation of work orders in MetaSolv – it was going well until I tried to find one of the work orders I created within the GUI. Aaand … they don’t show up. Before bothering sys admins with silly questions, I wanted to make sure I wasn’t somehow searching wrong. While most of the search dialog is easily correlated to the API XML input (responsible party is responsiblePerson from the XML, work order number is orderNumber, Description is description) … the little status buttons don’t have convenient tooltips to help decipher their meaning.

Ten minutes perusing the internal training documents yielded “select them all” which … yeah, I get. And it was nice to confirm that the “pushed in” button is selected. But that still doesn’t tell me what the little pictures mean.

So I searched the Internet, Oracle’s generally excellent documentation online, the F1 help within the app … nothing. Either these status values are so obvious to people who regularly use MetaSolv that it’s not worth mentioning or no one knows what these little buttons mean.

Which just made me more curious. So I performed a search limited to a single button, got a few work order numbers, and then looked the things up in the database tables. Numbering the buttons from left to right, I now have corresponding service_request_status values for each one:

Button # service_request_status
1 101
2 1
3 0
4 801
5 901

Fortunately, the back-end MSS documentation tells me what these status values mean:

0 – The service request has been entered and the tasks have been successfully generated and distributed to work queues.

1-99 – The service request is still being entered (tasks have not been generated and distributed to work queues).

101 – The service request has been electronically received but has not been processed.

801 – The service request has had its Due Date task completed.

901 – The service request has had all of its tasks, including billing, completed.