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.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
left outer join ds_dvrsty_set on ds_dvrsty_set.ds_dvrsty_set_id = ds_dvrsty_set_circuit.DS_DVRSTY_SET_ID
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) )
ds_dvrsty_set.ds_dvrsty_set_nm like '43%'
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.
I discovered the “rownum” trick early in my usage of Oracle databases — especially useful for sampling data to see what’s in there, something like “select * from dataTable where rownum < 6” gets you the first five records. But that’s not suitable if you want to sort the records. In this particular case, I have a series of names. I want to find the highest number value in the series so I can name my object with the next sequential name.
Enter “fetch first” … this appears to be available since 12c (so older database installations may still require a more convoluted solution):
SELECT set_name from set_data
WHERE set_name LIKE 'Something-With-A-Series-%'
ORDER BY set_name DESC
fetch first 1 row only;
Which returns the last name in the series.
I needed a query to find records where duplicate name values exist. I know how to group by and count, but the table has millions of records. I don’t want the 99% of the data where no duplication occurs. By using “having” in conjunction with “group by”, I am able to restrict the output to the groups that match my criterion.
select display_name, count(display_name) from circuit
group by display_name
having count(display_name) > 1;
My result set is the display name & occurrence count for that display name without all of the ‘good’ records where there’s a unique display name. (Yes, I know uniqueness could be enforced. The real scenario isn’t this straight-forward. There are times where the display name should be the same and I’ve got additional filters that drop out those cases).
I didn’t realize you couldn’t pass an arbitrary number of parameters to CONCAT in Oracle — you’ve got to chain your concatenations:
CONCAT(NL.CLLI_CODE, CONCAT('-',C.CIRCUIT_ID)) as TabName
To produce ABCDOH-12345
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.
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.
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