Results 1 to 4 of 4

Thread: Nested tables

  1. #1
    Join Date
    Apr 2007
    Posts
    51

    Unanswered: Nested tables

    I have a query with several nested table.
    For every selection, I have to select all the columns of the previous nested
    tables plus other columns I create.
    For example:

    select
    field1,
    field2,
    field1/field2 as field3
    from (
    select
    field1, field2
    from mytable.
    In the real query I have many columns to select and so in order to avoid to
    repeat all the fields names in every selection, I'd like to make this selection:

    select
    *,
    field1/field2 as field3
    i.e. I want to select all the columns of the previous table plus the new
    created column.
    How can that be done?
    Thank you.
    Anna - Verona (Italy)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select *
         , field1/field2 as field3
      from (
           select field1
                , field2
             from mytable
           ) as d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You will need to be using DB2 for z/OS V8.1. Common table expressions do not exist in DB2 V7 for z/OS (still supported by IBM).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by r937
    Code:
    select *
         , field1/field2 as field3
      from (
           select field1
                , field2
             from mytable
           ) as d

    "SELECT *" does not allow you to specify additional columns besides you use a correlation name with the *.


    SELECT D.* , field1/field2 as field3
    FROM (
    SELECT field1 , field2
    FROM my.table ) as D ;

    should work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •