Select data from first two columns of a table

r arunmoezhi · New User Joined: 26 Jun 2009 Posts: 32 Location: Chennai

I have a requirement to fetch data from the first two columns of a table. The column names are not known. Is there any way we can code like

PeterHolland · Posted: Mon Nov 30, 2009 5:52 pm

Ask the table owner, or return the requirement.

enrico-sorichetti · Posted: Mon Nov 30, 2009 6:16 pm

... and send it as a report to the two users sitting on the right side of the third office in the red corridor on the fourth floor of the second building

r arunmoezhi · New User Joined: 26 Jun 2009 Posts: 32 Location: Chennai

if you do not know the solution then dont bother the post.

if you think there is no solution to this question then say "its not possible"

if you think the requirements are not clear please say it direclty that you need further info.

or if you think the question is stupid please let me know and i will rephrase it.

why do you have to mock???

r arunmoezhi · New User Joined: 26 Jun 2009 Posts: 32 Location: Chennai

here is the more detailed version of the requirement.
i have to write a dynamic query which will update the table name

SELECT COL1,COL2 FROM TABLE_X

here TABLE_X is the table name passed to the query.
The name of the table will be dynamically provided and i have to fetch all the records from the table. (So there is no where clause). SInce the table name is a dynamic input and since i have to fetch only the first two columns, its impossible to know the actual column names upfront. But one thing which is known upfront is that whatever be the table, I need to fetch only the data from the first two columns.
so i was wondering if there is any way if we can provide the column position instead of column name.

P.S I hope people wont mock this again

dbzTHEdinosauer · Posted: Mon Nov 30, 2009 6:51 pm

no mocking, just rookie/novice terminology correction:

ROWS not records.

now, if this is to be dynamic sql within a program/script (COBOL, REXX, etc...)
you can prepare and execute 1 sql to obtain the column names,
and
you can then prepare and execute 1 sql to select the two columns from all rows of the table.

and before you ask,
there are numerous, recent examples of rexx,
and this Redbook discusses Dynamic Sql

and column position only works with ORDER BY and GROUP BY.

enrico-sorichetti · Posted: Mon Nov 30, 2009 7:25 pm

priyarc · New User Joined: 08 Oct 2009 Posts: 1 Location: bangalore

I have a requirement to fetch data from the first two columns of a table. The column names are not known. Is there any way we can code like

Code1
SELECT COL1,COL2 FROM OWNER.TABLE

Reply:
You can use column numbers instead of column name.

SELECT 1,2 FROM OWNER.TABLE

Craq Giegerich · Posted: Tue Dec 01, 2009 1:18 am

dbzTHEdinosauer · Posted: Tue Dec 01, 2009 4:19 am

yeah, and the return was 1 and 2.

Craq Giegerich · Posted: Tue Dec 01, 2009 4:39 am

dbzTHEdinosauer · Posted: Tue Dec 01, 2009 4:41 am

priyarc,

hello and welcome to the forum.

you are going to fit right in.

was worried as of late,
did not seem to be enough new posters who just posted without reading the other posts in the threads
or had an idea of IT
or generally believe that this is a chat room.

SELECT 1, 2 FROM TABLE
is select numeric literal 1 and numeric literal 2,
and as such populate the first 2 columns of the result table.

have you joined because
none of your co-workers will talk to you and have had enough of your drivel
or had you the mistaken impression that you had something to add?

GuyC · Posted: Tue Dec 01, 2009 9:17 pm

This is the kind of requirement, caused by some wannabe smart modeler:

Suppose you got n code-decode tables.

in table decode_attrib1 you've got colums : attrib1, desc_attrib1, timestamp_last_update, userid
in table decode_attrib2 you've got colums : attrib2, desc_attrib2, timestamp_last_update, userid
in table zipcodes you've got columns : zipcode, city,....
in table states you've got abbr_state, name_state, ....
in table country you've got ctry_iso, name_country,...

Now you want one program that handles all those tables.

I agree not the best way of doing it, but it is a legitimate request.

The answer : you will need to get the first two columns from sysibm.syscolumns

r arunmoezhi · New User Joined: 26 Jun 2009 Posts: 32 Location: Chennai

thanks guyC for taking pains to explain that my request was a legitimate one. my requirement was pretty much similar to the example which you have used in your post.

syscolumns table lookup is one method.. i thought there would be some direct way to select the column position. from the above replies i guess that there is no such direct way.

Terry Heinze · Posted: Wed Dec 02, 2009 6:30 am

r arunmoezhi,
As an epilog to this thread, please note the Joined date of enrico and the number of posts he has made over the years. Anyone participating on this Board as long as that has encountered so many of the types of posts here that a bit of cynicism is likely to creep in. It seems very few of the newer posters here have even taken the time to read the FAQ and posting guidelines and suggestions or have read other posts in order to avoid the pitfalls.