Named Row Data Type

Named Row Data Type

The third user-defined data type is the named row data type. With it, we can group values logically belonging to each other as one unit. For example, all values belonging to an address are grouped.

Figure. Create the named row data type called ADDRESS and use it in a CREATE TABLE statement.

 ```CREATE TYPE ADDRESS AS (STREET CHAR(15) NOT NULL, HOUSENO CHAR(4), POSTCODE CHAR(6), TOWN CHAR(10) NOT NULL) CREATE TABLE PLAYERS (PLAYERNO INTEGER PRIMARY KEY, NAME CHAR(15), : : RESIDENCE ADDRESS, PHONENO CHAR(13), LEAGUENO CHAR(4)) ```

Explanation Instead of having to define four columns in the CREATE TABLE statement, only one will do: RESIDENCE. That means that in one row in the column RESIDENCE, not one value, but a row with four values, is stored. This row of four values has a name (or, in other words, is named) of ADDRESS, which explains the term named row. The column RESIDENCE is a composite column. For each column belonging to a named row data type, a NOT NULL specification can be included.

Of course, you can use a data type several times in the same CREATE TABLE statement, for example:

```CREATE   TABLE PLAYERS
(PLAYERNO          INTEGER PRIMARY KEY,
:                 :
PHONENO           CHAR(13),
LEAGUENO          CHAR(4))
```

Working with composite columns affects the formulations of SELECT and other statements. We illustrate this with some examples.

9. Get the numbers and complete addresses of the players resident in Stratford.

 ```SELECT PLAYERNO, RESIDENCE FROM PLAYERS WHERE RESIDENCE.TOWN = 'Stratford' ``` The result is: ```PLAYERNO <=============== RESIDENCE ===========> STREET HOUSENO POSTCODE TOWN -------- -------------- ------- -------- --------- 6 Haseltine Lane 80 1234KK Stratford 83 Magdalene Road 16A 1812UP Stratford 2 Stoney Road 43 3575NH Stratford 7 Edgecombe Way 39 9758VB Stratford 57 Edgecombe Way 16 4377CB Stratford 39 Eaton Square 78 9629CD Stratford 100 Haseltine Lane 80 1234KK Stratford ```

Explanation In the SELECT clause, only one column has to be specified instead of four. Obviously, the result consists of five columns. The notation RESIDENCE.TOWN is new. This point notation indicates that only a part of the address is requested.

10. Get the numbers of the players living at the same address as player 6.

 ```SELECT OTHERS.PLAYERNO FROM PLAYERS AS P6, PLAYERS AS OTHERS WHERE P6.RESIDENCE = OTHERS.RESIDENCE AND P6.PLAYERNO = 6 ```

Explanation: Instead of a join condition on four columns (STREET, HOUSENO, TOWN, and POSTCODE), one simple join condition, in which the composite column is used, is sufficient.

Casting of values is also important with named row data types. We give you an example of a SELECT and an INSERT statement.

11. Get the number and name of the player living at the address 39 Edgecombe Way, Stratford, with postcode 9758VB.

 ```SELECT PLAYERNO, NAME FROM PLAYERS WHERE RESIDENCE = ADDRESS('Edgecombe Way', 39, '9758VB', 'Stratford') ```

Explanation: In this example, we can see clearly how the four values are cast into one ADDRESS value so that they can be compared with the column RESIDENCE.

12. Enter a new player.

 ```INSERT INTO PLAYERS (PLAYERNO, NAME, ..., ADDRESS, PHONENO, LEAGUENO) VALUES (6, 'Parmenter', ..., ADDRESS('Haseltine Lane', 80, '1234KK', 'Stratford'), '070-476537', 8467) ``` Named row data types are usually defined on base and distinct data types, but they can also be "nested." An example is given next. First, the data type POSTCODE is defined, consisting of two components: a part of four digits and a part of two letters. Next, this new named row data type is used in the definition of the ADDRESS data type. ```CREATE TYPE POSTCODE AS (DIGITS CHAR(4), LETTERS CHAR(2)) CREATE TYPE ADDRESS AS (STREET CHAR(15) NOT NULL, HOUSENO CHAR(4), POSTCODE POSTCODE, TOWN CHAR(10) NOT NULL) ```

13. Get the numbers and the full addresses of the players resident in postcode area 2501.

 ```SELECT PLAYERNO, RESIDENCE FROM PLAYERS WHERE RESIDENCE.POSTCODE.DIGITS = '2501' ```

14. Get the numbers and complete addresses of the players with postcode 1234KK.

 ```SELECT PLAYERNO, RESIDENCE FROM PLAYERS WHERE RESIDENCE.POSTCODE = POSTCODE('1234', 'KK') ```

Explanation: In the condition, two values are grouped into one value with a POSTCODE data type. A casting function is used for this.

In addition to the named row data type, some SQL products support the unnamed row data type. This data type also puts values together, but this group does not get a separate name.

```CREATE   TABLE PLAYERS
(PLAYERNO    INTEGER PRIMARY KEY,
NAME        CHAR(15),
:           :
RESIDENCE   ROW (STREET     CHAR(15) NOT NULL,
HOUSENO    CHAR(4),
POSTCODE   CHAR(6),
TOWN       CHAR(10) NOT NULL),
PHONENO     CHAR(13),
LEAGUENO    CHAR(4))
```

Explanation: We can see that the four values are grouped together here. However, no data type is defined explicitly. The effect of an unnamed row data type on SELECT and other statements is the same as that of the named row data type. The difference, however, is that the specification cannot be reused in several places. If there is also a MAILING_ADDRESS column, we must define the four subcolumns once again.

For casting of values, the word ROW is used:

```INSERT   INTO PLAYERS
(PLAYERNO, NAME, ..., ADDRESS, PHONENO, LEAGUENO)
VALUES  (6, 'Parmenter', ...,
ROW('Haseltine Lane', 80, '1234KK', 'Stratford'),
'070-476537', 8467)
```

 Exercise 33.2: What is wrong in the following SELECT statement? (We assume that the situation is the same as in Figure.) ```SELECT RESIDENCE FROM PLAYERS WHERE RESIDENCE LIKE '12%' ``` Exercise 33.3: Create the data type RESULT, consisting of two columns called WON and LOST, and use this new data type at the MATCHES table.