Using SQL with UniVerse:

UniVerse has supported SQL queries for a very long time. Prior to UniVerse version 10 files had to be designated as SQL tables with the CONVERT.SQL command. However since then SQL support is  built in.

One of the most powerful aspects of using SQL is the "uncorrelated subquery". If you know something about a record (row) and can query on that something, then other similar records (rows) can be found. For example to show the number of events of the same type and date as a given event something like the following should work:

Note: All programming displayed on this site is for illustrative purposes only. Use at your own risk.

---

SELECT COUNT(*) AS Events FROM EVENT_HISTORY WHERE DATE = (SELECT DATE FROM EVENT_HISTORY '<<Event number>>') AND TYPE = (SELECT EVENT.TYPE FROM EVENT_HISTORY '<<Event number>>') ;

---

Note that in UniVerse parlance  <<text>>  denotes a prompt that is presented and resolved at run-time.

Another use that I've used a lot is conditionally modifying individual values in a multivalued field (nested column). Generally speaking the syntax looks like this:

UPDATE filename SET mvfield = <value1,value2, …> WHERE condition;
or
UPDATE filename SET mvfield = value WHERE record-condition WHEN value-condition;

For example:

---

UPDATE EVENT_HISTORY SET TYPE = 'L' WHEN CODE = 'S' WHEN LOCATION MATCHING '101 S MAIN ST%';

---

I will explore INSERT and DELETE in another article.