Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Select data from first two columns of a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Mon Nov 30, 2009 5:47 pm    Post subject: Select data from first two columns of a table
Reply with quote

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
Code:

SELECT COL1,COL2 FROM OWNER.TABLE


ofcourse, we can obtain the column names from the SYSIBM.SYSCOLUMNS table based on their position, but how do we really feed in the column names obtained to the above query.
Back to top
View user's profile Send private message

PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Mon Nov 30, 2009 5:52 pm    Post subject:
Reply with quote

Ask the table owner, or return the requirement.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Nov 30, 2009 6:16 pm    Post subject: Reply to: Select data from first two columns of a table
Reply with quote

... 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


Quote:
I have a requirement to fetch data from the first two columns of a table.

the mother of the idiots is always pregnant icon_biggrin.gif
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Mon Nov 30, 2009 6:28 pm    Post subject:
Reply with quote

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???
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Mon Nov 30, 2009 6:36 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Nov 30, 2009 6:51 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Nov 30, 2009 7:25 pm    Post subject: Reply to: Select data from first two columns of a table
Reply with quote

Quote:
The column names are not known

what is the application value of something that is not known?
same thing as the mocking string I posted
the requirement asks for a box disregarding the content

Quote:
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???


I do know how to get the column names ( COLNO in sysibm.syscolumns )
I do know that there is a solution, but I do not like to waste time on stupid requirements
the requirement is clear, it' s a pity that it is stupid
it is not the question (in se ) that is stupid it' s the requirement,
no rewording will make the requirement more intelligent
mocking is an attempt to show the poor logic of the requirement
Back to top
View user's profile Send private message
priyarc

New User


Joined: 08 Oct 2009
Posts: 1
Location: bangalore

PostPosted: Tue Dec 01, 2009 1:05 am    Post subject: In reply to your question Arun,
Reply with quote

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
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Dec 01, 2009 1:18 am    Post subject: Re: In reply to your question Arun,
Reply with quote

priyarc wrote:
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


Did you test this?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Dec 01, 2009 4:19 am    Post subject:
Reply with quote

yeah, and the return was 1 and 2.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Dec 01, 2009 4:39 am    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
yeah, and the return was 1 and 2.


For every row in the table!
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Dec 01, 2009 4:41 am    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Dec 01, 2009 9:17 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Wed Dec 02, 2009 1:44 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Wed Dec 02, 2009 6:30 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 1 Tue Dec 06, 2016 4:38 am
No new posts How to move a long alphanumeric data ... lind sh COBOL Programming 5 Mon Dec 05, 2016 7:51 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us