|
View previous topic :: View next topic
|
| Author |
Message |
mfguy2008
New User
Joined: 02 May 2008
Posts: 3
Location: Columbus
|
|
|
|
Hello ,
Iam new to this forum.
I have a situation in one of my query.
The query looks like this
select * from employee
The result should be like
001 shaun
002 ron
003 mary
Here column shown as '001 002' are not part of my table.But I want the auto incremented number for each record in the table.
Can I get like this?
Please let me know if there is any solution for this issue.
Thanks |
|
| Back to top |
|
 |
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
Hi,
I think it's not doable by a 'query' only.
If you are running the query through Cobol, then you can keep your own counter. If you are doing ad hoc queries, what does the a row number generated by the select do for you ? BTW, without ORDER BY, query shown is not 'that' good.
To help You further, please check this link, a similiar topic:
www.ibmmainframes.com/viewtopic.php?t=26832&highlight=sequence
P.S.:
| Quote: |
| Iam new to this forum. |
Well every one was new at times, not to worry, you are welcome in the world for Forums...  |
|
| Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
| Back to top |
|
|
mfguy2008
New User
Joined: 02 May 2008
Posts: 3
Location: Columbus
|
|
|
|
I want to generate a comma delimited row in a db2 table.
The first entry information should be the auto incremented number like
001,kapil,cisco,......
002,ravi,IBM,....
How to get the number like 001 and 002 using a db2 query?
Please help me on this |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
I want to generate a comma delimited row in a db2 table.
|
Is this really true? I suspect that you want to read a db2 table and generate some comma delimited output.
An easy way to get what you want is to declare a cursor and as you fetch each row, increment a counter and STRING the counter plus any/all of the table fields you want delimited by commas into an output area. |
|
| Back to top |
|
|
mfguy2008
New User
Joined: 02 May 2008
Posts: 3
Location: Columbus
|
|
|
|
| dick scherrer wrote: |
Hello,
| Quote: |
I want to generate a comma delimited row in a db2 table.
|
Is this really true? I suspect that you want to read a db2 table and generate some comma delimited output.
An easy way to get what you want is to declare a cursor and as you fetch each row, increment a counter and STRING the counter plus any/all of the table fields you want delimited by commas into an output area. |
can you explain how to do this with a query? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| can you explain how to do this with a query? |
This would not be a single query that could be run via SPUFI. This would be embedded in a bit of code. You would declare the cursor, open the cursor, and then fetch rows individually, creating the csv data you want for output. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
Hi,
Your mission is little programatic, as suggested
| Quote: |
| This would not be a single query that could be run via SPUFI. |
alternatevly you could 'unload' the table & can write a sort-card in a single JOB for that. Might be there is way around but I've never used or come across. |
|
| Back to top |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
i also got a similar requirement where i had to increment the SN column of the table by 1 for each insert in the table. i.e. first the query had to read the last value in the SN column and then increment it by 1 for the insertion of the new row.
hope this may help you.
INSERT INTO MYTABLE SELECT SUBSTR ( MAX ( SN ), 1, 1 ) || CHAR ( INT ( SUBSTR ( MAX ( SN ), 2, 4 ) ) + 1 ) FROM MYTABLE;
here my column SN was like :
| Code: |
SN
-----
C1000
C1001
C1002
C1003
|
|
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
Hi,
In Your case "SN" column was 'part' of the table, here OP wants to add sequence number "in front of" the reords fetched via
| Code: |
| select * from tablename |
using which He gets something like
while he wants
| Code: |
001 shaun
002 ron
003 mary |
by a single query.
There is no such "SN" (Serial Number, I think) cloumn there in His table. |
|
| Back to top |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
OK, i think i was not able to understand his requirement clearly...  |
|
| Back to top |
|
|
|
|
