Presenting a Pick style dictionary as an SQL-like schema.

An alternative to explaining to SQL analysts how Pick-style dictionaries work, present the dictionary as an SQL schema instead:

At some point in their careers, custodians of a UniVerse (or UniData, D3, Pick, Caché, etc.) database will invariably be tasked with extracting data to import into a relational database like Microsoft SQL Server or MySQL. In my experience trying to explain how multivalue data is stored in a relational database to someone with only 1NF or 2NF databases experience is no easy task. Especially if the data being exported has a lot of multivalued associations. The result of exploding the multivalued associations is usually many related tables, with varying "depths" of rows for any one foreign key. The first step I've found that helps is to present a schema for the exported data that is familiar to the person tasked with importing the data.

I've created a new global dictionary item called COLUMN.CONVERSION  that converts the native conversion codes into their equivalent SQL column types. Here are some native (to UniVerse) conversion codes and the SQL counterpart:

Pick Conversions	SQL Data Types
D4/	date
MTS	time
MD2	real

A UniVerse dictionary I-type (S correlative in Pick) is not the easiest method for performing translations but is a reasonable place to start. One might be tempted to do the following:

001: I Output the corresponding SQL data type
002: "Date,Time,Real";"D4/ ,MTS ,MD2 ";IF LEN(F3) THEN INDEX(@2,F3,1) ELSE 0;IF @3 THEN @1[@3,4] ELSE ""
...

As simple and relatively short as this is, there are far too many combinations of conversion codes to expect a one-to-one translation. As such something more comprehensive is required. Instead of programming a subroutine which is my usual go-to hammer to address most nails, I'm going to show how to simply string together many IF-THEN-ELSE commands in an I-type to accomplish the task.

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

---

>ED DICT.DICT COLUMN.CONVERSION

0001: I
0002: TYPE;IF @1 = "D" OR @1 = "I" THEN F3 ELSE IF @1 = "A" OR @1 = "S" THEN F7 ELSE "";
      IF @2 MATCHES "'UDATTIM(D'1X0X'MT'0X" THEN "Datetime" ELSE @2;
      IF @3="D" OR @3 MATCHES "'D'1N0X" OR @3 MATCHES "'UDATTIM(D'0X" THEN "Date" ELSE @3;
      IF @4[1,1]="U" THEN @4[2,5] ELSE @4;
      IF @5 MATCHES "'MD'0X" THEN "Decimal" ELSE @5;
      IF @6[1,2]="MC" THEN "Text" ELSE @6;
      IF @7[1,2]="MT" THEN "Time" ELSE @7;
      IF @8="" THEN "Varchar(":MATCHFIELD(F5,"0N0X",1):")" ELSE @8;
      IF @9="CODE" THEN "Varchar(":MATCHFIELD(F5,"0N0X",1):")" ELSE @9
0003:
0004: Conversion}Code
0005: 12L
0006: S
...

---

The above I-type works using the simple concept that the prior semi-colon delimited section can be referenced with an "at" variable. The first semi-colon delimited section ("TYPE;") is evaluated to determine if it is a letter 'D', 'I', 'A' or 'S', in which case the second semi-colon delimited section is set to field three or field seven (or null). With each subsequent evaluation/assignment the @n (where 'n' is number representing the current number of semi-colon delimiters) is set.  After nine of these evaluation/assignments the result is sussed out. The result of the final evaluation/assignment is then what the I-type outputs.

Note: The I-type checks for the string "UDATTIM" in field three which is a custom "user exit" conversion that stores (or outputs) a date+time value (think @DATE+@TIME).

Here is an example of the output:

---

>LIST DICT INVENTORY @ID ASSOC ASSOC.A.IND ASSOC.CUSTODIAN ASSOC.DEPT ASSOC.LOCATION ASSOC.NO ASSOC.NO1 ASSOC.PREMISE ASSOC.REPORT.DATE ASSOC.STATUS ASSOC1.IND BAD.COND BAD.LOC BAD.PERM.LOC BIN CAPACITY CHANGE.DATE CHECKED.IN.CUSTODIAN CHECKED.IN.DATE CHECKED.IN.DEPT CHECKED.IN.LOC CHECKED.IN.LOC.DESC CHECKED.IN.NAME CHECKED.IN.ORG CHECKED.IN.QUANTITY CHECKED.IN.TIME CHECKED.IN.UNIT CHECKED.OUT.DATE CHECKED.OUT.DEPT CHECKED.OUT.LOC CHECKED.OUT.ORG CHECKED.OUT.QUANTITY CHECKED.OUT.UNIT CI.CUSTODIAN CI.STATUS COL.SPCS COLUMN.CONVERSION COLUMN.HEADING CONDITION CONDITION.DATE CONV.COND CONV.COND.COMMENTS CUSTODIAN.BIN CUSTODIAN.COMMENT CUSTODIAN.DATE CUSTODIAN.DISP CUSTODIAN.FROM CUSTODIAN.INV.ID CUSTODIAN.LOC CUSTODIAN.TIME CUSTODIAN.TO DATE.CREATED DATE.UPDATED DEFAULT.BIN DEFAULT.CAT DEFAULT.CHECKED.IN.DEPT DEFAULT.CHECKED.IN.ORG DEFAULT.CHECKED.OUT.DATE DEFAULT.CHECKED.OUT.DEPT DEFAULT.CHECKED.OUT.ORG DEFAULT.CITY DEFAULT.COND DEFAULT.CSI.TYPE DEFAULT.CUSTODIAN.FROM DEFAULT.CUSTODIAN.LOC DEFAULT.CUSTODIAN.TO DEFAULT.OLS DEFAULT.RES.CITY DEFAULT.RES.ST DEFAULT.RES.ZIP DEFAULT.REVIEW DEFAULT.REVIEW.DATE DEFAULT.ST DEFAULT.ZIP DEPT DICT DMI.INVENTORY.DATA ENTRY.DATE FIELD FIELD.FMT IMAGE.COUNT IN.ASSOC IN.POSSESSION INSPECTION INSPECTION.DATE INV.TYPE INV.TYPE.DESC INVENTORY INVENTORY.ID ITEM ITEM.BARCODE.VALUE ITEM.CATEGORY ITEM.NO KEY0 KEY1 KEY2 KEY3 LABEL.DATE LETTER.DATE LIST.NO LOCATION LOST.DATE LOT MM.DEVELOP.DATE MODEL NOTFOUND.IND OAN OWNER OWNER.BUS.NAME OWNER.DOB OWNER.FIRSTNAME OWNER.FULLNAME OWNER.ID OWNER.LASTNAME OWNER.MIDDLENAME OWNER.OLN OWNER.OLS OWNER.RES.ADDRESS OWNER.RES.CITY OWNER.RES.PHONE OWNER.RES.STATE OWNER.RES.ZIP OWNER.SEX PERMANENT.LOC PERMANENT.LOC.ROOM PHARMA.SOURCE PHARMA.TYPE PRIVATE PROP.COUNT PROPERTY.COUNT PROPERTY.CUSTODIAN RECEIPT.ADDL.DOB RECEIPT.ADDL.FIRSTNAME RECEIPT.ADDL.LASTNAME RECEIPT.ADDL.MIDDLENAME RECEIPT.ARREST2.OFFICER RECEIPT.BOOK.CUSTODIAN RECEIPT.COMPARED RECEIPT.DATE RECEIPT.DOB RECEIPT.EVENT RECEIPT.EVENT2 RECEIPT.FIRSTNAME RECEIPT.HOURS RECEIPT.LASTNAME RECEIPT.MIDDLENAMME RECEIPT.OFFICER RECEIPT.RESULT RECEIPT.TRANS RELEASE.AUTH RELEASE.TO REMOVE.DATE REPORT.DATE RETENTION REVIEW.DATE SERIAL.NO SM.ITEM.BARCODE.VALUE SPECIAL STORED.BY STORED.DATE SYSTEM.MISC1 SYSTEM.MISC2 SYSTEM.MISC3 SYSTEM.MISC4 SYSTEM.MISC5 TODAY UPDESC USE.DATE USE.LOCATION USE.TIME USER.DEPT USER.MISC1 USER.MISC2 USER.MISC3 USER.MISC4 USER.PADEPT USERNAME VAR VAR.IN.USE WHO.CREATED WHO.UPDATED HDR.SUP ID.SUP COL.SPCS

Field............................ Conversion..                    Output.........
Name............................. Code........ ColHdg............ Format.........
@ID                               Varchar(20)  INVENTORY No        20L
ASSOC                             Varchar(15)  ASSOC No            15L
ASSOC.A.IND                       Varchar(15)  ASSOC DEPT Index    15L
ASSOC.CUSTODIAN                   Varchar(6)   ASSOC CUSTODIAN     6R
ASSOC.DEPT                        Varchar(5)   ASSOC DEPT          5L
ASSOC.LOCATION                    Varchar(30)  Location            30L
ASSOC.NO                          Varchar(10)  ASSOC.NO No         10R
ASSOC.NO1                         Varchar(8)   ASSOC No            8R
ASSOC.PREMISE                     Varchar(20)  Premise             20L
ASSOC.REPORT.DATE                 Date         ASSOC Report Date   10R
ASSOC.STATUS                      Varchar(2)   ASSOC Status        2L
ASSOC1.IND                        Varchar(5)   ASSOC DEPT Index    5L
BAD.COND                          Varchar(4)   Bad Last COND       4L
BAD.LOC                           Varchar(3)   Bad Loc             3R
BAD.PERM.LOC                      Varchar(3)   Bad Loc             3R
BIN                               Varchar(4)   Bin                 4L
CALL                              Varchar(8)   ASSOC.NO No         8R
CALL.TYPE                         Varchar(6)   Call Type           6L
CAPACITY                          Varchar(3)   CAPACITY Fired      3R
CHANGE.DATE                       Date         Change Date         10R
CHECKED.IN.CUSTODIAN              Varchar(6)   Recov Off           6R
CHECKED.IN.DATE                   Date         CHECKED.INy Date    10R
CHECKED.IN.DEPT                   Varchar(33)  DEPT                33L
CHECKED.IN.LOC                    Varchar(30)  Location            30L
CHECKED.IN.LOC.DESC               Varchar(32)  Desc                32L
CHECKED.IN.NAME                   Text         CHECKED.IN Name     20L
CHECKED.IN.NAME                   Text         CHECKED.IN Name     20L
CHECKED.IN.ORG                    Varchar(26)  ORG                 26L
CHECKED.IN.QUANTITY               Decimal      Quantity            8R
CHECKED.IN.TIME                   Time         CHECKED.IN Time     5R
CHECKED.IN.UNIT                   Varchar(10)  Unit                10L
CHECKED.OUT.DATE                  Date         CHECKED.OUT Date    10R
CHECKED.OUT.DEPT                  Varchar(33)  DEPT                33L
CHECKED.OUT.LOC                   Varchar(30)  Location            30L
CHECKED.OUT.ORG                   Varchar(26)  ORG                 26L
CHECKED.OUT.QUANTITY              Decimal      Quantity            8R
CHECKED.OUT.UNIT                  Varchar(10)  Unit                10L
CI.CUSTODIAN                      Varchar(6)   CI CUSTODIAN        6R
CI.STATUS                         Varchar(2)   Status              2L
CONDITION                         Varchar(12)  COND                12L
CONDITION.DATE                    Date         COND Date           10R
CONV.COND                         Varchar(40)  COND'd              40L
CONV.COND                         Varchar(4)   COND'd              4L
CONV.COND.COMMENTS                Varchar(40)  COND'd              40L
CUSTODIAN.BIN                     Varchar(4)   Bin                 4L
CUSTODIAN.COMMENT                 Varchar(60)  Comments/Remarks    60T
CUSTODIAN.COND                    Varchar(2)   COND                2L
CUSTODIAN.DATE                    Date         CUSTODIANODIAN Date 10R
CUSTODIAN.FROM                    Varchar(15)  Transfer From       15L
CUSTODIAN.INV.ID                  Varchar(6)   CUSTODIAN  INV No   6L
CUSTODIAN.LOC                     Varchar(12)  Location            12L
CUSTODIAN.TIME                    Time         Time                5L
CUSTODIAN.TO                      Varchar(15)  Transfer To         15L
DATE.CREATED                      datetime     Entered             16R
DATE.UPDATED                      datetime     Changed             16R
DEFAULT.BIN                       Varchar(4)   Dflt Bin            4L
DEFAULT.CAT                       Varchar(2)   Dflt Cat Cod        2L
DEFAULT.CHECKED.IN.DEPT           Varchar(4)   DFLT REC AGCY       4L
DEFAULT.CHECKED.IN.ORG            Varchar(2)   DFLT RECOV ORG      2L
DEFAULT.CHECKED.OUT.DATE          Date         Dflt CHK.OUT Date   10R
DEFAULT.CHECKED.OUT.DEPT          Varchar(4)   DFLT STOL AGCY      4L
DEFAULT.CHECKED.OUT.ORG           Varchar(2)   DFLT CHK.OUT ORG    2L
DEFAULT.CITY                      Varchar(15)  DFLT CITY           15L
DEFAULT.COND                      Varchar(2)   Dflt COND           2L
DEFAULT.CSI.TYPE                  Varchar(3)   Dflt CSI Type       3L
DEFAULT.CUSTODIAN.FROM            Varchar(12)  Dflt CUSTODIAN From 12L
DEFAULT.CUSTODIAN.LOC             Varchar(12)  Dflt CUSTODIAN Loc  12L
DEFAULT.CUSTODIAN.TO              Varchar(12)  Dflt CUSTODIAN To   12L
DEFAULT.OLS                       Varchar(2)   DFLT OLS            2L
DEFAULT.RES.CITY                  Varchar(15)  DFLT RES CITY       15L
DEFAULT.RES.ST                    Varchar(2)   DFLT RES ST         2L
DEFAULT.RES.ZIP                   Varchar(10)  DFLT RES ZIP        10L
DEFAULT.REVIEW                    Date         Dflt Review Date    10R
DEFAULT.REVIEW.DATE               Date         Default Rev Date    10R
DEFAULT.ST                        Varchar(2)   Dft St              2L
DEFAULT.ZIP                       Varchar(10)  DFLT ZIP            10L
DEPT                              Varchar(4)   DEPT                4L
DMI.INVENTORY.DATA                Varchar(15)  DMI DATA            15L
ENTRY.DATE                        Date         Entry Date          10R
IMAGE.COUNT                       Decimal      Image Count         5R
IN.ASSOC                          BOOL         In ASSOC            3R
IN.POSSESSION                     Varchar(3)   In Pos              3L
INSPECTION                        Varchar(25)  INSPECTION          25L
INSPECTION.DATE                   Date         INSPECTION Date     10R
INV.TYPE                          Varchar(2)   INV Type            2L
INV.TYPE.DESC                     Varchar(25)  INVENTORY Type      25L
INVENTORY.ID                      Varchar(6)   INV No              6R
ITEM                              Varchar(4)   Weap Type           4L
ITEM.BARCODE.VALUE                Varchar(10)  Item BC Value       10L
ITEM.CATEGORY                     Varchar(1)   WEAP CAT            1L
ITEM.NO                           Varchar(3)   Item No             3R
KEY0                              Varchar(8)   Prop/INV ID         8R
KEY1                              Varchar(4)   DEPT                4L
KEY2                              Varchar(8)   INVENTORY No        8R
KEY3                              Varchar(4)   Item No             4R
LABEL.DATE                        Varchar(10)  Label Date          10R
LETTER.DATE                       Date         Letter  Date        10R
LIST.NO                           Varchar(5)   List                5R
LOCATION                          Varchar(15)  Current Location    15L
LOST.DATE                         Date         Lost Date           10R
LOT                               Varchar(10)  Lot Ref             10L
MM.DEVELOP.DATE                   Date         MM DEVELOP DATE     10R
MODEL                             Varchar(4)   MODEL               4L
NOTFOUND.IND                      Varchar(1)   Not Rec             1L
OAN                               Varchar(20)  Owner No            20L
OWNER                             Varchar(6)   OWNER No            6R
OWNER.BUS.NAME                    Text         Business Name       40L
OWNER.BUS.NAME                    Text         Business Name       40L
OWNER.FIRSTNAME                   Varchar(15)  First               15L
OWNER.FULLNAME                    Text         OWNER Name          30L
OWNER.ID                          Varchar(11)  Owner ID            11L###-##-####
OWNER.LASTNAME                    Varchar(15)  Last Name           15L
OWNER.MIDDLENAME                  Varchar(10)  Middle              10L
OWNER.OLN                         Varchar(10)  Owner OLN           10L
OWNER.OLS                         Varchar(2)   Owner OLS           2L
OWNER.RES.ADDRESS                 Varchar(20)  Address             20L
OWNER.RES.CITY                    Varchar(12)  City                12L
OWNER.RES.PHONE                   PHON         Telephone           14L
OWNER.RES.STATE                   Varchar(2)   St                  2L
OWNER.RES.ZIP                     Varchar(10)  ZIP                 10L
PERMANENT.LOC                     Varchar(25)  Permanent Location  25L
PERMANENT.LOC.ROOM                Varchar(4)   INV Room            4L
PHARMA.SOURCE                     Varchar(2)   Source              2L
PHARMA.TYPE                       Varchar(20)  PHARMA/Narcotics    20L
PRIVATE                           BOOL         No Share            3R
PROP.COUNT                        Varchar(15)  Prop Items          15L
PROPERTY.COUNT                    Decimal      Prop Items          5R
PROPERTY.CUSTODIAN                Varchar(6)   Prop CUSTODIAN      6L
RECEIPT.ADDL.DOB                  Date         DOB                 10R
RECEIPT.ADDL.FIRSTNAME            Varchar(12)  First               12L
RECEIPT.ADDL.LASTNAME             Varchar(12)  ADDL Last Name      12L
RECEIPT.ADDL.MIDDLENAME           Varchar(10)  Mid                 10L
RECEIPT.BOOK.CUSTODIAN            Varchar(15)  Jailer ID           15L
RECEIPT.COMPARED                  Varchar(15)  Comparison          15L
RECEIPT.CUSTODIAN                 Varchar(15)  CUSTODIAN           15L
RECEIPT.DATE                      Date         Date                10R
RECEIPT.DOB                       Date         DOB                 10R
RECEIPT.EVENT                     Varchar(10)  RBPD EVENT          10L
RECEIPT.EVENT2                    Varchar(20)  EVENT Other         20L
RECEIPT.EVENT2.CUSTODIAN          Varchar(15)  Jailer ID           15L
RECEIPT.FIRSTNAME                 Varchar(12)  First               12L
RECEIPT.HOURS                     Decimal      Hours               4L
RECEIPT.HOURS                     Decimal      Hours               5L
RECEIPT.LASTNAME                  Varchar(12)  Last Name           12L
RECEIPT.MIDDLENAME                Varchar(10)  Mid                 10L
RECEIPT.RESULT                    Varchar(15)  Results             15L
RECEIPT.TRANS                     Varchar(15)  INVENTORY RECEIPT   15L
RELEASE.AUTH                      Varchar(13)  Authorized By       13L
RELEASE.TO                        Varchar(12)  Release To          12L
REMOVE.DATE                       Date         Remove  Date        10R
REPORT.DATE                       Date         Report Date         10L
RETENTION                         Varchar(4)   Ret Code            4L
REVIEW.DATE                       Date         Review  Date        10R
SERIAL.NO                         Varchar(6)   SERIAL.NO           6L
SM.ITEM.BARCODE.VALUE             Varchar(12)  Item BC Value       12L
SPECIAL                           BOOL         Pro tct             3R
STORED.BY                         Varchar(10)  STORED BY           10L
STORED.DATE                       Date         STORED DATE         10R
SYSMISC1                          Varchar(6)   Reserved System1    6L
SYSMISC2                          Varchar(6)   Reserved System2    6L
SYSMISC3                          Varchar(6)   Reserved System3    6L
SYSMISC4                          Varchar(6)   Reserved System4    6L
SYSMISC5                          Varchar(6)   Reserved System5    6L
TODAY                             Date         Today               10R
UPDESC                            Varchar(45)  DESC                45L
USE.DATE                          Date         USE Date            10R
USE.LOCATION                      Varchar(35)  USE Location        35L
USE.TIME                          Time         USE Time            5R
USER.DEPT                         Varchar(4)   User Agcy           4L
USER.DEPT                         Varchar(4)   User DEPT           4L
USER.MISC1                        Varchar(6)   Reserved User1      6L
USER.MISC2                        Varchar(6)   Reserved User2      6L
USER.MISC3                        Varchar(6)   Reserved User3      6L
USER.MISC4                        Varchar(6)   Reserved User4      6L
USER.MISC5                        Varchar(6)   Reserved User5      6L
USERNAME                          Varchar(9)   User ID             9L
VAR                               Varchar(12)  VAR                 12L
VAR.IN.USE                        BOOL         Inv In Crim         3R
WHO.CREATED                       Varchar(9)   By                  9L
WHO.UPDATED                       Varchar(9)   By                  9L

185 records listed.

---

This dictionary-to-SQL-schema is more meaningful to the dbms analyst not versed in multivalued databases.

How to determine if a dictionary item is possibly a code table lookup

How to determine if a dictionary item is possibly a code table lookup I-Type:

This article describes how to use an I-Type to determine all dictionary items that are also code table lookups. Up to now when I want to find any dictionary item that is a code table lookup I visually list the dictionary, looking for any records with field two containing the TRANS() function and the KEY argument equaling the code field in question. For example, SEX is a typical field in a dictionary, containing either "M" or "F" (or some other codes as needed). When programmers or analysts want to output either "Male" or "Female" instead of the code then an I-Type is usually constructed to output the word that describes the code. A sex field (or any other field with just a few possible codes) can simply use an I-Type with simple IF-THEN-ELSE logic. For example:

001: I Output the sex description
002: IF SEX="M" THEN "Male" ELSE IF SEX="F" THEN "Female" ELSE "?"
...

However, many code fields can potentially contain many more values... sometimes in the thousands or tens of thousands of possible values. In these cases an I-Type constructed as follows would be appropriate:

001: I Output the item description
002: TRANS("PARTS.FILE",PART.NUM,DESC,"X")
...

The syntax of the TRANS() function is pretty basic but I'll explain it anyway. Argument one is the table name to perform the lookup on (PARTS.FILE in this case). Argument two is the record ID to be looked up (read). In this case the record ID of the PARTS.FILE table is the same as the PART.NUM field in this file. The third argument (DESC) is the field to return. This can also be hardcoded as an integer, which is the number of the field in the table record to return. Obviously, when using the field name (ex: "DESC") it must exist as a D-Type dictionary item in the table. Finally, the fourth argument dictates what to return if the record doesn't exist. I usually use the X value which results in nothing being returned when the record doesn't exist. Other values are V (returns null but displays an error indicating the record doesn't not exist), C (returns the table record ID if the record does not exist), and N (returns the table record ID if the results of the read (contents of the field) are null).

There are other ways to return code descriptions as well. XLATE, OCONV with the "Tfile" conversion code, custom "user exits", etc. Most code table translations that I use are of the type that utilizes TRANS and a user-exit conversion code that performs a translation.

For this exercise, we'll need to have access to either all dict items that contain the current dict item ID in field two or need to know if the current dict item field two contains the dict item ID in question.

In my first iteration, I wanted to select only dict items containing the current field ID in field two, but UniVerse complained (note that the new dictionary item is in DICT.DICT. This is so that it is available when looking at any file or table dictionary.):

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

---

0001 Subroutine DETTABLOO(out,mode)
...
0019 if not(init) then
0020    *
0021    * Initialize things...
0022    *
0023    init = true
0024    open "DICT",@FILENAME to D.FILE else error = true
0025 end
...
0044    cmd = "SELECT DICT ":@FILENAME:" WITH F2 MATCHES "
0045    cmd := '"0X':"'":@ID:"'":'0X"'
0046    cmd := " TO 8;"
0047    execute cmd capturing dummy
0049    done = false
0055    loop
0056       tmp = ""
0057       readnext dict.key from 8 then
...

>LIST DICT.DICT "IS.TABLE.LOOKUP" COL.SPCS

                Type &
Field.......... Field. Field........... Column.... Output Depth &
Name........... Number Definition...... Heading... Format Assoc..
IS.TABLE.LOOKUP I SUBR('DETTABLOO','D') Is Item    70L    M
                                        Code Table
                                        Lookup?

>SORT DICT PARTS IS.TABLE.LOOKUP

               Is item........................................
Field......... Code Table.....................................
Name.......... Lookup?........................................
@              Non-SQL re-entrant query calls are not allowed.
@ID            Non-SQL re-entrant query calls are not allowed.
PART.NUM       Non-SQL re-entrant query calls are not allowed.
VENDOR         Non-SQL re-entrant query calls are not allowed.
A_PARTS        Non-SQL re-entrant query calls are not allowed.

...

---

UniVerse does not like embedding SELECT statements in I-Types (and I'll be the first to admit it's probably bad practice). Next, I created a list of the dictionary items:

---

>SELECT TO SLIST 0 FROM DICT PARTS
SQL+WHERE TYPE='D' OR TYPE='I'
SQL+ORDER BY FIELD.NO, TYPE;

254 record(s) selected to SELECT list #0.

>>SAVE.LIST PARTS.DICT.ITEMS

---

Then I changed the subroutine called by the new I-Type to read this previously saved list:

---

>CT BP DETTABLOO

 1 Subroutine DETTABLOO(out,mode)
 2 *
 3 * Program DETECT.TABLE.LOOKUP
 4 *  This routine should be called from an I-type.
 5 *  Note: It will be called by every field in an Inform/RetrieVe statement.
 6 *
 7 * Arguments:
 8 *  out  : (output) Results of subroutine
 9 *  mode : (input)  If 'D' then output found I-type details. Otherwise only ID.
10 *
11 equ true to 1
12 equ false to 0
13 common /dettabloo/ init,dict.keys,D.FILE
14 results = ""
15 out = ""
16 error = false
17 truncate.pos = 70
18 if @RECCOUNT <= 1 then init = false ;* Force reinitialize if new query
19 if not(init) then
20    *
21    * Initialize things...
22    *
23    init = true
24    open "DICT",@FILENAME to D.FILE else error = true
25    dict.keys.list = @FILENAME:".DICT.ITEMS"
26    open "&SAVEDLISTS&" to F.SAVEDLISTS then
27       read dict.keys from F.SAVEDLISTS, dict.keys.list else
28          out<1,-1> = "Read Er! &SAVEDLISTS& ":dict.keys.list:"."
29          error = true
30       end
31    end else
32       out<1,-1> = "Unable to open &SAVEDLISTS&!"
33       error = true
34    end
35 end
36
37 if len(dict.keys) and not(error) then
38    match.pattern = '0X':"'TRANS('1X0X',":@ID:",'0X"
39    oconv.fn.match.pattern = '0X':"'OCONV(":@ID:",'0X'UCODES'0X"
40    more = true
41    cnt = 0
42    dict.keys = dict.keys ;* reset the pointer
43    loop
44       tmp = ""
45       remove dict.key from dict.keys setting more
46       if len(dict.key) and dict.key <> @ID then       ;* Shouldn't display "itself"
47          cnt += 1
48          read rec.dict from D.FILE, dict.key then
49             begin case
50                case upcase(trim(convert(" ","",rec.dict<2>))) matches match.pattern
51                   tmp = dict.key
52                case upcase(trim(convert(" ","",rec.dict<3>))) matches "'UCODES'0X" and rec.dict<2> = @ID
53                   tmp = dict.key
54                case upcase(trim(convert(" ","",rec.dict<2>))) matches oconv.fn.match.pattern
55                   tmp = dict.key
56             end case
57             if len(tmp) and upcase(trim(mode))[1,1] = "D" then
58                *
59                * Include details from the found I-type...
60                *
61                tmp := " ":rec.dict<1>[1,1]:"~":rec.dict<2>:"~":rec.dict<3>:"~":rec.dict<6>
62             end
63          end else tmp = "Read er '":dict.key:"'"
64       end
65       if len(tmp) then
66          results<1,-1> = tmp[1,truncate.pos]
67       end
68       until not(more)
69    repeat
70    out = results
71 end
72 return
73 end

---

The result is now the query can be run, returning only matching columns:

---

>SELECT @ID, IS.TABLE.LOOKUP FROM DICT PARTS
SQL+WHERE IS.TABLE.LOOKUP<>''
SQL+ORDER BY @ID;

           Is Item............................................
Field..... Code Table.........................................
Name...... Lookup?............................................
HIST.DISP  HIST.DISP.DESC I~HIST.DISP~UCODES*HIST.CODES~Desc~2

PART.ID    IN.AP I~LEN(TRANS(AP.DETAIL, INVOICE.NUM, PART.ID, 
           INVENTORY.ID I~SUBR('!CATS',TRANS(INVENTORY,PART.ID

PART.TYPE  PART.TYPE.DESC I~PART.TYPE~UCODES*PART.CODES~Desc~1

TYPE       TYPE.DESC I~TRANS(PART.TYPE,TYPE,DESC,'C')~~Desc~10
           TREAT.DESC I~PROCESS;IF @1#'' THEN TRANS(TREATMENT,
...

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.

Active Directory and @LOGNAME:

In my experience so much depends on the value of the user ID (@LOGNAME). Security and access are frequently assigned by user IDs. Even the initial interaction with a logged in user can depend on the user ID (I'm referring to how in Pick mode if there is a VOC item record ID that is the same as the user ID then that will be executed (if it is a sentence, paragraph, verb, remote, or Proc) before the VOC "LOGIN" item.)

The problem arises when users authenticate with Active Directory (instead of the passwd file) and web based UIs will accept any combination of uppercase and lowercase letters for the user ID. Users accustomed to having access to menu items and to update records may be surprised when the system presents a strange menu, does not allow access, or displays information in read-only mode (or all three). Users authenticating from a different domain will introduce another component to the mix by having the domain included in the @LOGNAME variable  (e.g. Domain/userID).

If the application security and/or access uses either all uppercase or all lowercase user IDs then one quick fix is to overwrite the @LOGNAME variable early in the process, prior to having security checked and access given. Below is a very small program to check if @LOGNAME is all lowercase and if not initializes it to all lowercase letters:

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

---

$options internal
*
* Overwrite the @LOGNAME with all lowercase if it is mixed case...
*
if @LOGNAME <> OCONV(@LOGNAME,"MCL") then
   new.logname = OCONV(@LOGNAME,"MCL")
   @LOGNAME = new.logname
   SWAP @LOGNAME, new.logname
end

end

---

Reformatting text values:

This article describes how to use a simple I-type to format an existing D-type field with any number of multi-values containing text, into regularly sized values of text. While replicating data from UniVerse to Microsoft SQL Server I ran across some data that varied widely in its format with regards to the length of multi-valued text. The problem was evident when inserting the individual values as separate rows in the SQL Server database. The schema had been set to some arbitrary column width (for example VARCHAR(255)) which worked well for most of the records but for many the text length exceeded this limit and was truncated to fit the schema. One option would be to insert the contents of the field, including value-marks as a Binary Large OBject (BLOB) in its own column. This would make future queries difficult so a better option is to make the replicated data fit the schema.

Here is what the data looks like (I display some calculated values on-the-fly to help illustrate the problem):

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

---

>LIST EVENTS EVAL "DCOUNT(DESC,@VM)" COL.HDG "#vals" FMT "4R" EVAL "LEN(REUSE(DESC))" FMT "10R" COL.HDG "Len Desc" EVAL "CONVERT(@VM,'}',DESC)" COL.SPCS BY.DSND EVAL "LEN(REUSE(DESC))" COUNT.SUP

EVENTS #vals Len Desc CONVERT(@VM,'}',DESC)...................
130196     1     9998 While emailing from the tablet app the 
                      full text was not completely visible at
                      all times. Instead I had to utilize the
                      vertical and horizontal scroll bars and
...
160283    18     1273 Choosing to print displays the print 
                      dialog but the "Print" and "Cancel" 
                      buttons aren't immediately visible. By
                      scrolling the app "up" the buttons are
                      visible.}Sometimes I can zoom in and
...
140119    17      792 Running the latest version the option to
                      automatically activate the map with my
                      current location isn't apparent.}After
                      changing views to map-view then by
                      clicking the use-current-location icon
                      will the map appear with my location.}
...
021092     2       66 There are periodic flashes that occur at
                      random times.}See attached.
...
181002     1       46 The font size of the field labels is too
                      small.

---

In the above example the list is sorted in descending order by the DESC field length. The largest field is 9,998 characters in length and the smallest field contains just 46 characters.

The problem that becomes apparent is that the replicated data, being split off into an associated table with foreign keys that link it to the base table row, is not formatted consistently. Obviously had the issue of varying lengths of values been anticipated then the entry window that initially captures the information could have been designed to always result in regularly sized values. With that said the most expedient solution is to replicate a new dictionary item in place of the "DESC" data field, one that "chunks" the output into regularly sized values that are replicated into individual rows. This will ensure all data conforms to the schema constraints and subsequent queries will return all matching rows.

The I-type used for this purpose is as follows:

---

>LIST DICT EVENTS "DESC" "DESCRIPTION"

               Type &
Field......... Field. Field........................................................ Column......... Output Depth &
Name.......... Number Definition................................................... Heading........ Format Assoc..
DESC           D   12                                                               Description     40T    M
DESCRIPTION    I      TRIM(CONVERT(@TM,@VM,FMT(TRIM(CONVERT(@VM,' ',DESC)),'80T'))) Description     80T    M

2 records listed.

---

The original field is "DESC" which is a D-type item. The new field is "DESCRIPTION" which is an I-type item. The new item inserts value marks every 80th character. What follows are the same records shown above, but with the DESCRIPTION field alongside that, for the purposes of displaying what's happening, also converts the @VMs (value marks) to "}" just for the sake of visualizing them in the sample listing. The actual fields as rendered by RetrieVe appear as line breaks when listed.

---

>LIST EVENTS EVAL "CONVERT(@VM,'}',DESC)" EVAL "CONVERT(@VM,'}',DESCRIPTION)" COL.SPCS BY.DSND EVAL "LEN(REUSE(DESC))" COUNT.SUP

EVENTS CONVERT(@VM,'}',DESC)................... CONVERT(@VM,'}',DESC)...................
130196 While emailing from the tablet app the   While emailing from the tablet app the  
       full text was not completely visible at  full text was not completely visible at 
       all times. Instead I had to utilize the  }all times. Instead I had to utilize the
       vertical and horizontal scroll bars and  vertical and horizontal scroll bars and}
...
160283 Choosing to print displays the print     Choosing to print displays the print     
       dialog but the "Print" and "Cancel"      dialog but the "Print" and "Cancel" 
       buttons aren't immediately visible. By   }buttons aren't immediately visible. By
       scrolling the app "up" the buttons are   scrolling the app "up" the buttons are
       visible.}Sometimes I can zoom in and     }visible. Sometimes I can zoom in and
...
140119 Running the latest version the option to Running the latest version the option to
       automatically activate the map with my   automatically activate the map with my 
       current location isn't apparent.}After   }current location isn't apparent. After
       changing views to map-view then by       changing views to map-view then by
       clicking the use-current-location icon   }clicking the use-current-location icon
       will the map appear with my location.}   will the map appear with my location.
...
021092 There are periodic flashes that occur    There are periodic flashes that occur
       at random times.}See attached.           at random times. See attached.
...
181002 The font size of the field labels is too The font size of the field labels is too
       small.                                   small.

---

The result is that very large fields with none, one or a few value marks are broken up into discrete 80 character (or fewer) chunks. These segments will fit within a SQL Server table column with type VARCHAR(80). Any DESC fields that are not very large will result in a DESCRIPTION field that aggregates the values into one or a few value "chunks".

While purposefully entered line breaks (in the original field) will not always be preserved in the new aggregated field, the output does conform to a SQL Server schema without truncating anything.