I have been using LISTAGG to group a bunch of records together to be presented in a single HTML table cell. Problem is LISTAGG doesn’t do anything with null field values. As such, the data doesn’t line up across columns. The three ID values have two string values, which basically get centered in the cell. You cannot tell which ID value goes to which name value.
By adding a concatenation to the LISTAGG value, something will be included in the result set even when the record value is null.
Voila — records line up and I can tell the first ID doesn’t have an associated string value.
I needed to add a few HTML-rendered spaces to a LISTAGG … which meant I needed to figure out a way of getting an ampersand into the glue string. Using the concat (||) feature, I was able to glom ‘&’ and ‘nbsp;’ together as the glue:
I wanted a quick way to query a logging table for today’s records. I figured there had to be some way to put a variable “today” into the query rather than put in the numbers for the current date. Voila — a query that retrieves records where the timestamp is greater than or equal to today:
where action_ts >= cast(trunc(current_timestamp) as timestamp)
order by action_ts desc;
I wanted to filter my result set to items where a column contained a value from another column — not that it was equal, but like. CONCAT allows me to do this:
nlA.clli_code LIKE CONCAT('%', CONCAT(nle.exchange_area_clli ,'%'))
Alternately, using ||
nlA.clli_code LIKE ('%' || nle.exchange_area_clli || '%')
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
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
Quick statement to find the uptime for an Oracle database
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.v_$instance;