Tech Tip: Formatting Queries Without A Dictionary Descriptor in UniVerse
Log onto any MultiValue application that has been in existence for three years, find a commonly used file, say CUST, and list it’s dictionary. What do you see? Definitions for LNAME, LAST.NAME, L.NAME, LST.NAME, LNAME40, etc. Upon closer examination you discover that field (attribute) is also defined as X, XX, PGM030.LNAME.RPT, and on and on. Log on to an application that has been in use for 30 years and you could find several hundred dictionary entries for a file with only 20 fields.
Obviously what has happened is that if LNAME is originally defined as 15 characters wide and a report wants to use it in a column 13 characters wide, there you go. Another dictionary entry. Then in an ad hoc query, someone wants the data in a column 20 characters wide. Plop. There’s another “temporary” definition that never gets cleaned up.
In UniVerse there is a solution to this problem. All of the formatting things you can do from a dictionary descriptor you can do from the command line, or from within a Paragraph, Proc, EXECUTE, etc.
Staying with column width as an example, it’s quite simple. In your query statement, after naming the column you want displayed, simply add the justification and width specification with the FMT keyword. For example,
LIST CUST LNAME FMT “35L”
Conversion codes are simply specified with the CONV keyword.
LIST CUST LNAME FMT “35L” BAL.DUE CONV “MD24,$”
Column headings, anyone? The key word is COL.HDG. Also notice that the column heading text can include the ‘L’ option to break the heading into multiple lines.
LIST CUST LNAME FMT “35L” BAL.DUE CONV “MD2,$” COL.HDG “Amt’L’Owed”
But what if you want to perform a calculation or some kind of string manipulation? Then you have to use an I-descriptor, don’t you? As you might suspect, the answer is no. Anything you can code in the expression of an I-descriptor can be done on the command line using the EVAL keyword. Let’s combine the last name and the first initial to give us the names of the customers listed.
LIST CUST EVAL “LNAME:’, ‘:FNAME[1,1]”
While these are useful for ad hoc queries, these keywords can also be used to write permanent reports that are relatively immune to someone changing a descriptor and having it affect your report, like making it switch to non-columnar format, for example. Remembering that a line can end in an underscore character to indicate “continued on the next line,” you might write a Paragraph like this.
PA SORT CUST WITH BAL.DUE > “0” _ EVAL “LNAME:’, ‘:FNAME[1,1]” FMT “30L” COL.HDG “Name” _ BAL.DUE CONV “MD2,$” FMT “10R” COL.HDG “Amt Owed” _ HEADING “Customer Owing Us Money” _ LPTR
For additional command line formatting abilities, see chapter 3 of the UniVerse Guide to Retrieve manual.