Pick / Universe / MDISL / Reality / Unidata Data Dictionaries

One of the most flexible areas of a multivalued database is the availability of dictionaries for reporting. This paper details dictionaries in the generic PICK style.

Typically each Pick (data) file will have an associated DICTionary file. This contains shorthand items to reference and report on

• Specific fields in the datafile
• Related fields in other files via a file translate
• Generate columns based on conditional equations IF .. THEN .. ELSE etc

Dictionaries can also be present in the MD FILE or VOC FILE – These can be potentially used by any data file.

A simple dictionary in a dictionary of a file called HOUSE_SALES called CUSTOMER is shown below:

CUSTOMER

<1> A

<2> 2

<9> R

<10> 7

This will generate a column 8 characters wide (ie ‘Customer’ is 8 wide, this is used in preference to the value of field or attribute 10). It assumes that the data file called CUSTOMER holds the customer number in attribute / field 2

If the customer number refers to another file called CUSTOMER_DETAILS which contains the customer surname, forename and title in fields 1,2 and 3, then the dictionary below will produce the forename:

CUSTOMER_FORENAME

<1> S

<2> 2

<8> TCUSTOMER_DETAILS;C;3;3

<9> R

<10> 7

In the dictionary, the starting field value is taken from the attribute number in <2>. If there are any Correlatives in <8> these will be applied, then any Conversions in <7>

In the CUSTOMER_FORENAME dictionary, field 2 from the file will be used as the starting point. This will be a customer number.

The Correlative in field <8> will then be processed.

This is a translate into the CUSTOMER_DETAILS file bringing back field 3

The ‘C’ tells the ENGLISH processor to return the original key if a record is not found. If ‘X’ was used, then null would be returned instead.

Conversions and Correlatives, what’s the difference?

A whole list of conversions for files, dates, numbers etc are available and can be used in either or both conversions and correlatives

Conceptually a conversion is used for some change in the format of data. For example, a date may be stored in the file as a pick internal date (ie the number of days since the 31st December 1967), but we wish to display the data in one of the everyday formats such as ‘18 Jan 2004’.

Correlatives are for translations of data from one format, like a code, into another format, the second format being defined by a record on another file.

The crucial difference is in how English / Access / UniQuery processes conversions and correlatives.

When the Access / Uniquery interprets a dictionary definition, correlatives are processed immediately. The resulting values are then used in any sort criteria that have been specified. Conversions are processed after the sort but just before output. To illustrate the use of this, consider that example of a date.

Since dates are held on the database as the number of days since the 31st December 1967, it is a good idea to use this format to sort the data, that is, sort the data numerically. Just before output, but after the sort, the data is converted into its external format. It follows from this that dates held in this way should have corresponding dictionary definition that use the conversion field to tell Access how to convert the data, and also be right justified so that the data is sorted in numerical rather than alphabetical sequence.

In this way we can obtain a chronological sort. If we took a date in its real format, such as 24 JAN 1998, and tried to sort this either numerically or alphabetically, we would not succeed. For example, in both cases 02 NOV 1999 would precede 24 JAN 1998. This is what would happen if the date conversion were to be specified in the correlative field. So use these two with care!

Multiple conversions / correlatives can be combined by use of a Multivalue marker (Char(253))

Arithmetic Conversions

A; N(TOTAL_VALUE) / N(QUANTITY)

To reference another named dictionary N( [name] ) can be used.

To reference the value of a field in this data file, just use its field number

Operators such as + , - , * , / can be used

Above a calculation of average price per item is being done.

A; N(SURNAME)[ "1" , "5"]

Square brackets can be used for substringing.

Above, the 1^st 5 characters of the surname dictionary are extracted. Note the quotes!

A; IF ( N(SURNAME)["1","5"] ) = "SMITH" THEN "SMITH" ELSE (N(SURNAME)["1","1"]

Brackets can be used to clarify precedence

A Colon ( : ) can be used to indicate concatenation.

IF must always be followed by THEN and ELSE

If more than one IF/ELSE is used it must follow this pattern: IF [cond] = [true cond] THEN x ELSE IF [cond2] # [untrue cond] THEN y ELSE ""

Above, if the surname starts with SMITH then SMITH is displayed, otherwise just the first letter is displayed.

A; S(3)

The S() function will summate the chosen attribute

S() can also include +-

(LPV) can be used to Load Previous Value

I-Descriptors (I Type dictionaries)

These allow a catalogued data-basic subroutine to be called from a U2 , Universe or Unidata dictionary. Data Basic calls

To call a (compiled and catalogued LOCAL COMPLETE) subroutine in field 2, use the following syntax :

SUBR( "[ProgramName]", [Parm1],[Parm2]..)

The databasic subroutine will look something like this:

SUBROUTINE [ProgramName]( DICTRESULT, PARMS1 , PARMS2)

DICTRESULT = ""

IF PARMS1 = "…" THEN

DICTRESULT = ..

END ELSE

DICTRESULT = ….

END

RETURN

END

The internal databasic @variables @ID , @RECORD and @PARA.SENTENCE can all be used in these subroutines.

HOTPIX Knowledge > Universe+PICK > Dictionaries