MacLochlainns Weblog

Michael McLaughlin’s Technical Blog

Site Admin

Joining nested tables

with 3 comments

Nested tables let you implement ID dependent tables. ID dependent tables are solely dependent on another table. Data modelers choose between ID- and non-ID dependent tables. The non-ID dependent table is the most common. It describes implementing ordinary tables. ID dependent tables require you to nest a table in another. Most designers opt for non-ID dependent relationships because they avoid nesting tables.

Some teams denormalize the table by creating multiple columns. A classic example is an address table. Since an address can have multiple street address values because of building, suite, and apartment numbers. You’ve probably seen them implemented as STREET_ADDRESS1, STREET_ADDRESS2, et cetera. The problem with this design is that a customer may require third street address column. An alternative is to implement a comma delimited list (technically, violating first normal form).

The following implements an ID dependent model or nested table:

1. Create a scalar collection type, implement the collection in an address table, and seed it with two rows. You raise a PLS-00329 exception if you attempt to assign a multiple column structure as a base datatype of a user-defined type (UDT). UDTs are by default collecitons of schema-level scalar datatypes. A scalar datatype contains only one thing, like a number or string.

CREATE TABLE contact_book
( contact_id  NUMBER
, first_name  VARCHAR2(20)
, middle_name VARCHAR2(20)
, last_name   VARCHAR2(20));
 
INSERT INTO contact_book VALUES ( 1,'Brad',NULL,'Scott');
INSERT INTO contact_book VALUES ( 2,'Jeff',NULL,'Kenney');

2. Create a scalar collection type, implement the collection in an address table, and seed it with two rows:

CREATE TYPE street_list IS TABLE OF VARCHAR2(30);
/
 
CREATE TABLE address_book
( address_book_id NUMBER
, street_address  STREET_LIST
, city            VARCHAR2(30)
, state           VARCHAR2(2)
, postal_code     VARCHAR2(10))
NESTED TABLE street_address STORE AS street_table;
 
INSERT INTO address_book VALUES
( 1, street_list('203 North La Salle','Suite 1400'), 'Chicago', 'IL', '60601');
INSERT INTO address_book VALUES
( 2, street_list('203 North La Salle','Suite 1800'), 'Chicago', 'IL', '60601');

3. Create a function to convert the varying number of rows into a single string with line returns (that’s the CHR(10) call):

CREATE OR REPLACE FUNCTION format_address(set_in STREET_LIST) RETURN VARCHAR2 IS
  retval VARCHAR2(2000);
  CURRENT NUMBER;
BEGIN
  IF set_in IS NOT NULL AND set_in IS NOT EMPTY THEN
    CURRENT := set_in.FIRST;
    WHILE CURRENT IS NOT NULL LOOP
      retval := retval || set_in(CURRENT)||CHR(10);
      CURRENT := set_in.next(CURRENT);
    END LOOP;
  END IF;
  RETURN retval;
END format_address;
/

4. Query the collection with the FORMAT_ADDRESS function, which resolves the one to many join between the scalar columns and array returned by the TABLE function:

SELECT   cb.first_name
||       DECODE(cb.middle_name,NULL,' ',cb.middle_name||' ')
||       cb.last_name||CHR(10)
||       format_address(ab.street_address)
||       ab.city||', '
||       ab.state||' '
||       ab.postal_code||CHR(10) mail_label
FROM     contact_book cb JOIN address_book ab ON cb.contact_book_id = ab.contact_book_id
WHERE    ab.street_address IS NOT NULL
OR       ab.street_address IS NOT EMPTY;

This provides you with a formatted address list for envelops:

Brad Scott
203 North La Salle
Suite 1400
Chicago, IL 60601
 
Jeff Kenney
203 North La Salle
Suite 1800
Chicago, IL 60601

How to test the state of nested columns?

Somebody posted a comment and asked how you could determine if a nested table was null. I thought showing how you can find null, empty, or populated collections would be more helpful. Here’s a quick example that uses the same scalar collection of strings found earlier in this post. You can create this sample case by using the preceding code, plus the following:

Insert another two rows. One with a NULL value for the STREET_ADDRESS column and another with an empty SQL collection in the same column, like this:

INSERT INTO address_book VALUES                  -- Inserts a NULL collection.
( 3, NULL, 'San Francisco','CA','94106');
INSERT INTO address_book VALUES                  -- Inserts an empty collection.
( 4, street_list(), 'Los Angeles','CA','90017');

You can query to determine which rows have a NULL value, an empty SQL collection, or a populated SQL collection. The following query uses the CASE statement to return the values:

SELECT   address_book_id
,        CASE
           WHEN street_address IS NULL  THEN 'Null'
           WHEN street_address IS EMPTY THEN 'Empty'
           ELSE 'Populated'
         END AS content
,        city
FROM     address_book;

It returns the following data:

ADDRESS_BOOK_ID CONTENT   CITY
--------------- --------- --------------
              1 Populated Chicago
              2 Populated Chicago
              3 Null      San Francisco
              4 Empty     Los Angeles

A quick note: If you try to select the contents of the populated contents by using the column name, it raises the following error because the native type is really a SQL collection. Unfortunately, my test schema is named PLSQL, but this is a SQL data type.

SQL> SELECT   address_book_id
  2  ,        CASE
  3             WHEN street_address IS NULL  THEN 'Null'
  4             WHEN street_address IS EMPTY THEN 'Empty'
  5             ELSE street_address
  6           END AS content
  7  ,        city
  8  FROM     address_book;
           ELSE street_address
                *
ERROR at line 5:
ORA-00932: inconsistent datatypes: expected CHAR got PLSQL.STREET_LIST

You can see the data by rewriting the query this way. The table that contains the nested table must precede the nested table column as an argument to the TABLE function. As you can see it repeats the non-repeating data types because of the cross join (Cartesian product). That’s why a normalizing function was used earlier in the blog post.

The join does not work against rows containing either a null or empty collection because the TABLE function can’t process them. Therefore, you needn’t worry about them in this scenario.

SQL> SELECT   address_book_id
  2  ,        CASE
  3             WHEN street_address IS NULL  THEN 'Null'
  4             WHEN street_address IS EMPTY THEN 'Empty'
  5             ELSE nested_table.column_value
  6           END AS content
  7  ,        city
  8  FROM     address_book CROSS JOIN TABLE(street_address) nested_table;
 
ADDRESS_BOOK_ID CONTENT                        CITY
--------------- ------------------------------ ------------------------------
              1 203 North La Salle             Chicago
              1 Suite 1400                     Chicago
              2 203 North La Salle             Chicago
              2 Suite 1800                     Chicago

Hope this helps a few folks.

Written by maclochlainn

May 25th, 2008 at 10:59 pm

Posted in

3 Responses to 'Joining nested tables'

Subscribe to comments with RSS or TrackBack to 'Joining nested tables'.

  1. How can we check for nested column whether it is null or not as we cannot use NVL on nested column

    swapnil

    23 Dec 08 at 11:33 pm

  2. I’ve added a bunch of content to address the question in the modified post. I hope it helps you.

    The quickest way is to query like this to find rows that contain a null value for a nested table:

    SELECT   rowid
FROM     some_table
WHERE    some_collection_column IS NULL;

    Also, an not-null but empty collection can be found by using this syntax:

    SELECT   rowid
FROM     some_table
WHERE    some_collection_column IS EMPTY;

    A populated collection column is found by checking for the absence of the first two conditions, like:

    SELECT   rowid
FROM     some_table
WHERE    some_collection_column IS NOT EMPTY
AND      some_collection_column IS NOT NULL;

    maclochlainn

    24 Dec 08 at 11:50 pm

  3. Thank you!

    Steve

    3 Mar 09 at 2:05 pm

Leave a Reply