Skip to content
CA Datacom Core - 15.1
Documentation powered by DocOps

Get Started with CA Dataquery

Last update January 2, 2019

Before you begin to construct your own queries, dialogs, saved sets, terms or personal tables, you should know something about:

  • The structure of a database
  • Database terminology
  • How a query works
  • The purposes of dialogs, terms, and saved sets

The following list provides definitions for commonly-used database terminology, as well as a foundation in database structure.

Authorization ID (authid)

Each user who works in SQL Mode has a special ID called the Private SQL Authorization ID. It is the user's default authorization ID for personal database and for all SQL Mode. If you change your SQL authorization ID with the PROFILE or AUTHID command, it changes only on the User Profile. If you create a personal table, your default authorization ID is attached to the table name regardless of the authid you were using when you created the table.

Column and Row

To access the tables in your database with queries, you need to know their table names. To get to the values in each row, you need to know the column names. A column name is a label for a specific type of information, for instance, NAME or ADDRESS.

The horizontal components of a table are called rows (sometimes called records). Since the columns of a table are defined with a specific sequence, data type, and number of positions, each row of a table has identical column definitions. Rows within a specific table are differentiated from each other by their sequence in the table and by the values assigned to each column.

Column and Row


    ┌──────────┬──────────┬──────────┬─────────────┐
    │   LAST   │   FIRST  │  CITY    │   STATE     │
    ├──────────│──────────│──────────│─────────────┤
    │  SMITH   │  MARY    │ ATLANTA  │  GEORGIA    │
    ├──────────│──────────│──────────│─────────────┤
    │  ALLEN   │  JOE     │ PARIS    │  TEXAS      │  ◄── Row
    ├──────────│──────────│──────────│─────────────┤
    │  WILSON  │  ALICE   │ BAILY    │   TEXAS     │
    └──────────┴──────────┴──────────┴─────────────┘
                       ▲
                       │
                       │
                     Column

Compound Fields

CA Datacom®/DB provides the ability to give a single column name to a group of contiguous columns and to refer to the group by that name. Each individual column also has a name. This data structure can also be called a compound field.

Compound Field


    ┌─────────────────────┐
    │          NAME       │
    ├──────────┬──────────│──────────┬──────────┐
    │   LAST   │   FIRST  │  CITY    │   STATE  │
    ├──────────│──────────│──────────│──────────┤
    │  SMITH   │   MARY   │ ATLANTA  │  GEORGIA │
    └──────────┴──────────┴──────────┴──────────┘

Join

Suppose you were required to provide a report listing the name, employee number, state, insurance code, and number of dependents for employees with dependents. The Personnel Table contains some of the information, arranged by employee number. The Benefits table contains the rest of the information, arranged by insurance code. Since the name column exists in each table, the query can use it to relate the rows in the tables to each other, whenever the same name appears in each table.

Joining the rows found in the first table to rows in other tables temporarily creates a new kind of table called a found set that contains the columns you need from each table, as in the following illustration. CA Dataquery™ for CA Datacom® retrieves the data for query output from the found set.

Simple Equijoin Example


          Personnel Table         Benefits Table
        ┌────┬───────┬────┐     ┌───┬───────┬─────┐
        │ NO │ NAME  │ ST │     │ C │ NAME  │ DEP │
        ├────│───────│────┤     ├───│───────│─────┤
        │  1 │ SMITH │ NY │     │ A │ SMITH │  2  │
        ├────│───────│────┤     ├───│───────│─────┤
        │  2 │ JONES │ OK │     │ B │ WHITE │  3  │
        ├────│───────│────┤     ├───│───────│─────┤
        │  3 │ GRAY  │ NY │     │ C │ GRAY  │  2  │
        └────┴───┬───┴────┘     └───┴──┬────┴─────┘
                 │                     │
                 └──────────┬──────────┘
                            │
                            │
                            │
                            │
                            ▼
                        Found Set
             ┌───────┬────┬────┬───┬─────┐
             │ NAME  │ NO │ ST │ C │ DEP │
             ├───────│────│────│───│─────┤
             │ SMITH │  1 │ NY │ A │  2  │
             ├───────│────│────│───│─────┤
             │ GRAY  │  3 │ NY │ C │  2  │
             └───────┴────┴────┴───┴─────┘

The previous illustration shows the traditional equijoin concept, meaning that tables will be joined when the common key or column contains equal values in each related table, and only joined rows will appear in the output.

At CA Dataquery™ for CA Datacom® Version 10.0, we provided the ability to perform outer joins, thus including specific unrelated rows in the found set. 

Key

CA Datacom®/DB provides the ability to define one or more keys for a table. In DQL Mode only, keys are specially defined columns or groups of columns whose definitions are stored in the CA Datacom®/DB index along with directions to the location of actual data. A group of columns can be defined as one key and is known as a compound key. Using keys to join tables means CA Dataquery™ for CA Datacom® users can quickly retrieve data because CA Dataquery™ for CA Datacom® does not read whole tables to find the locations of needed rows.

Repeating Fields

With CA Dataquery™ for CA Datacom® in DQL Mode, you can take advantage of the ability of CA Datacom®/DB to duplicate the structure of a column to create another column for cases where there might be more than one occurrence of a piece of information, as in monthly figures or family dependent information. This type of data structure can be referred to as a repeating field.

Repeating Fields


          ┌──────────┬───────────────────────────────────────────────┐
          │ CUST-NO  │ MONTHLY-PAYMENT                               │
          ├──────────│───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┤
          │00039432  │ 17│ 17│ 17│ 17│ 17│ 17│   │   │   │   │   │   │
          ├──────────│───│───│───│───│───│───│───│───│───│───│───│───┤
          │00039982  │ 18│ 18│ 18│ 18│ 18│ 18│ 18│ 18│ 18│   │   │   │
          ├──────────│───│───│───│───│───│───│───│───│───│───│───│───┤
          │00044524  │ 23│ 23│ 23│ 23│ 23│ 23│ 23│   │   │   │   │   │
          └──────────┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┘

In this example, MONTHLY-PAYMENT is a repeating field. There is one occurrence for each month of the current year.

A repeating field can consist of simple or compound fields. Each repetition of a repeating field is called an occurrence. Each occurrence in a repeating field can also be a repeating field. The entire set of occurrences is called an array. To refer in a query to a specific data item in an array, use a subscript, as in the following example:

PRINT FLDB(2,1)

The (2,1) denotes the second occurrence of FLDA and the first occurrence of FLDB within FLDA. See the following illustration for clarification.

Two-level Array (Repeating Field)

┌──────────────────────┬───────────────────────┬────────────────────────┐
│        FLDA          │         FLDA          │         FLDA           │
├──────────┬───────────│───────────┬───────────│───────────┬────────────┤
│    FLDB  │    FLDB   │      FLDB │    FLDB   │    FLDB   │    FLDB    │
└──────────┴───────────┴───────────┴───────────┴───────────┴────────────┘

CA Dataquery™ for CA Datacom® queries can access one- and two-level arrays. If an array consists of more than one level, only the first two levels can be accessed by a query, and then only if the levels are adjoining. The following examples show how to reference various types of occurrences in a simple repeating field.

Two-level Array (Repeating Field)


     ┌────────────────────────────┬──────────────────────────────┐
     │              FLDA          │            FLDA              │
     ├─────────────┬──────────────│──────────────┬───────────────┤
     │        FLD  │       FL     │        FLDB  │       FLD     │
     └─────────────┴──────────────┴──────────────┴───────────────┘

To reference in a PRINT statement:

  • First FLDA
    PRINT FLDA(1)
  • Second FLDA
    PRINT FLDA(2)
  • First FLD
    PRINT FLDB(1,1)
  • FL
    PRINT FLDB(1,2)
  • FLDB
    PRINT FLDB(2,1)
  • Second FLD
    PRINT FLDB(2,2)

Repeating fields can be used in any statement that permits naming a regular column or key, that is, WITH, SET, SORT, WHEN/DO.

Schema

In SQL Mode, when a user is added with a private SQL authorization specified, CA Dataquery™ for CA Datacom® automatically creates a schema in CA Datacom® Datadictionary™ for the SQL authorization ID. A schema defines the individual user's SQL environment. A schema contains all table, view, and privilege definitions owned by a given authorization ID.

Synonym

In SQL Mode, the use of a synonym provides an alternate name for a table. The synonym is a convenience that allows the user to avoid naming both the authorization ID and the table name (authid. tablename) in the query. It has no effect on security. The CREATE SYNONYM command names the authorization ID of the owner and the alternate table name. The other user accesses the table by using the alternate table name in the queries.

Table

Value

A value is actual data at an assigned row and an assigned column in a table. For instance, the column named LAST-NAME might have many values, such as SMITH, THOMAS, WILSON, and so on.

View

In SQL Mode, a view is a portion of a table that permits access to some, but not all of the data. See the CA Datacom®/DB documentation for more information on the use of views.

For more information:

Was this helpful?

Please log in to post comments.