Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Unanswered: Access column by number in result set

    I have a bunch of queries that is stored in a file provided by a vendor and I know that it always returns rows with 2 columns in them. For eg:

    Code:
    SELECT EMPID, NAME FROM EMPLOYEE;
    SELECT BOOK_ID, BOOKNAME FROM BOOKS;
    However I cannot modify them in anyway.

    I want to use these statements in a generic sql statement like 'SELECT col2 from (vendor-sql-statement)' to access values in column 2. For eg:

    Code:
    -- Get all the employee names without the employee ids:
    select [col2] from (SELECT EMPID, NAME FROM EMPLOYEE) 
    -- Get all the book names without the book ids:
    select [col2] from (SELECT BOOK_ID, BOOKNAME FROM BOOKS)
    Is there a way of accessing doing this in DB2?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How(or from what client program) do you want to execute the statements?
    If you read the statements into your program, then I thought that you can modify them.

    Anyway, if you want to use the statements as is,
    how about enclosing them by "SELECT col2 FROM (" and ") t(col1, col2)"?

    For example:
    Code:
    SELECT col2 FROM (SELECT EMPID, NAME FROM EMPLOYEE) t(col1, col2)
    SELECT col2 FROM (SELECT BOOK_ID, BOOKNAME FROM BOOKS) t(col1, col2)

  3. #3
    Join Date
    Jul 2009
    Posts
    8

    Solved!

    Thanks for the reply, that solved it.

    I was never aware that we could specify such alias columns names in that syntax.

    Thanks again!


    Quote Originally Posted by tonkuma View Post
    How(or from what client program) do you want to execute the statements?
    If you read the statements into your program, then I thought that you can modify them.

    Anyway, if you want to use the statements as is,
    how about enclosing them by "SELECT col2 FROM (" and ") t(col1, col2)"?

    For example:
    Code:
    SELECT col2 FROM (SELECT EMPID, NAME FROM EMPLOYEE) t(col1, col2)
    SELECT col2 FROM (SELECT BOOK_ID, BOOKNAME FROM BOOKS) t(col1, col2)

Posting Permissions

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