Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: Inheritance query in DB2

    I have two objects which represent an inheritance.
    Employee inherits from Person. To represent this, I have two tables: TPerson and TEmployee. I want to SELECT from both tables at the same time using the UNION ALL.
    I want to do a UNION SELECT from both tables in the one query.
    The problem is that the UNION syntax means that you must have the same number of columns in both tables.
    There are some columns in TEmployee that are not in TPerson. For example suppose column EmployeeNumber is not in TPerson.
    In oracle I can do:

    SELECT EmployeeNumber, x1, x2, x3
    FROM TEMPLOYEE
    UNION ALL
    SELECT null as EmployeeNumber, x1, x2, x3
    FROM TPERSON.

    However I can't do this in DB2, I try to do:

    SELECT EmployeeNumber, x1, x2, x3
    FROM TEMPLOYEE
    UNION ALL
    SELECT cast(null as INT), x1, x2, x3
    FROM TPERSON

    but this does not work.

    I get: DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703 i.e. An undefined column, attribute, or parameter name was detected.

    Any ideas?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Are you sure EmployeeNumber is INT?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    SQL0206N "<name>" is not valid in the context where it is
    used.

    Explanation:

    This error can occur in the following cases:
    ...

    o For a SELECT or DELETE statement, the specified column is not
    a column of any of the tables or views identified in a FROM
    clause in the statement.
    Check the spelling.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you give the DLL (create table) scripts for both tables ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    you might also try


    SELECT EmployeeNumber, x1, x2, x3
    FROM TEMPLOYEE
    UNION ALL
    SELECT NULLIF( 1 , 1 ), x1, x2, x3
    FROM TPERSON

Posting Permissions

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