MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Joining nested tables

with 11 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. I recently added a set of INSERT, UPDATE, and DELETE statements for nested tables in this post and expanded that with how to update a table view column (avoiding an ORA-25015 error).

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

Updates into these types and how to avoid an ORA-25015 error and in notes. Hope this helps a few folks.

Written by maclochlainn

May 25th, 2008 at 10:59 pm

Posted in Uncategorized

11 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

  4. it isn’t clear if you can populate a nested table with the results of a select statement. Is that possible and how do you do it?

    james

    20 May 11 at 10:39 am

  5. You can’t really do it because the way you access the data is through a CROSS JOIN, and that turns the number of rows into a product of the container row times the nested table rows. There’s no way to disambiguate that. At least, I’ve not found one.

    It’s easier to use PL/SQL to update elements of nested tables. However, you can use an UPDATE statement to replace the contents of a nested table.

    Here’s a quick example of what doesn’t work, even if you got this close to the syntax, which should work and may yet work in the future. It deals with a multiple level nested table:

    SQL> UPDATE TABLE(SELECT  addr.street_address
  2               FROM    employee e CROSS JOIN TABLE(e.home_address) addr
  3               WHERE   e.employee_id = 1
  4               AND     addr.address_id = 1)
  5  SET column_value = 'Suite 522'
  6  WHERE column_value = 'Suite 521';

    It returns the following error message:

    SET column_value = 'Suite 522'
    *
ERROR at line 5:
ORA-25015: cannot perform DML ON this nested TABLE VIEW COLUMN

    The error occurs because you must replace the entire contents of a VARRAY or nested table when it’s composed of a scalar data type, like a NUMBER, VARCHAR2, and so forth. Here’s how you would update the collection correctly:

    SQL> UPDATE TABLE (SELECT e.home_address
  2                FROM   employee e
  3                WHERE  e.employee_id = 1) e
  4  SET e.street_address = street_list('1111 Broadway','Suite 522')
  5  WHERE e.address_id = 1;

    maclochlainn

    21 May 11 at 9:51 pm

  6. […] update table view columns […]

    update table view columns

    20 Dec 11 at 11:16 pm

  7. col1 col2 col3
    1 r 1,2,3,4

    2 t 1,2,4,5,6

    3 h 1,2,3,4,5

    out put

    select r get 1,2,3,4

    how can we get output like aboue

    srikanth

    2 Apr 12 at 12:00 am

  8. col1 col2 col3
    1 r 1
    2
    3
    4

    srikanth

    2 Apr 12 at 12:02 am

  9. I’d like to help but you need to provide a bit more information. If it’s a nested table, does the nest column contain the number sequence? If so, do you want to match the sequence against the containing row?

    maclochlainn

    2 Apr 12 at 1:06 am

  10. Could you send me an answer for this question?

    TABLE1
sno     name
----    ----
1       ename
2       empno
3       sal
5       job
 
TABLE2:-
sno     ename   empno   sal    job
1        vdc    644     6445   fbf
2        dffb   535     3355   cfd
3         -     535     3445   dfd
4        feg    335     4535   sgd

    I want data if we give sno or name from TABLE1 give the TABLE2 data.

    krish

    14 Jul 12 at 8:34 pm

  11. Are the SNO columns surrogate keys? If so, they’ve no relation in a join. It appears that the TABLE1.NAME column has nothing to do with the TABLE2.ENAME column. If that’s correct you have no keys to join. That would leave a CROSS JOIN, with a filter in the WHERE clause. That’s all I can do with what you’ve provided.

    maclochlainn

    15 Jul 12 at 12:21 am

Leave a Reply