Access column by number in result set

raks81 · #1 (**permalink**) 01-04-12, 01:13

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?

tonkuma · #2 (**permalink**) 01-04-12, 02:00

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)

raks81 · #3 (**permalink**) 01-04-12, 02:11

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

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)

Internet Services

Web Development

Digital Marketing

Consumer Tech