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
 

 

Cursor for generating sequence numbers

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

Moderator


Joined: 17 Oct 2006
Posts: 2180
Location: @my desk

PostPosted: Fri Jan 11, 2008 1:53 pm    Post subject: Cursor for generating sequence numbers
Reply with quote

Hi,

I have two tables T1 & T2 with columns T1_SEQ(integer) and T2_SEQ(integer) respectively

I need a cursor which will give all the numeric values which is not present in T1 and T2.

Code:


T1_SEQ              T2_SEQ
------              ------
1                     4
2                     6
3                     9


Code:

Cursor Output
-------------
5
7
8



I have some alternatives. But I want to know whether it is possible or not.

Thanks,
Arun
Back to top
View user's profile Send private message

stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Jan 11, 2008 6:29 pm    Post subject:
Reply with quote

It is not possible. The result set you describe has an infinite number (well up to the largest number an integer type can be) of rows in it. The values an integer can store range from -2,147,483,648 to +2,147,483,647.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2180
Location: @my desk

PostPosted: Fri Jan 11, 2008 9:23 pm    Post subject:
Reply with quote

Hi Steve,

I m sorry for not making it clear. I need only the values till MAX(T2_SEQ).
we are using db2 v7

Thanks,
Arun
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jan 11, 2008 9:29 pm    Post subject:
Reply with quote

?? you want a result set based on rows that are not there?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Fri Jan 11, 2008 9:35 pm    Post subject:
Reply with quote

It could be done for the seq that is in one table but not the other but I'm not sure about a number missing from both tables. It may be possible but the expense may be high.
Back to top
View user's profile Send private message
rashmi.ranjan75

New User


Joined: 20 Feb 2006
Posts: 34

PostPosted: Fri Feb 22, 2008 8:49 pm    Post subject:
Reply with quote

after looking into the example given by you it seems that you want to fetch all the numbers which are

- less than the max number in T2
- not present in any of the tables T1 or T2

correct me if i am wrong...becz requirement is not very clear..
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 Incorrect output when trying to add n... monica1 PL/I & Assembler 10 Fri Jan 13, 2017 5:02 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Sequence number add in SORT pshongal SYNCSORT 3 Fri Sep 02, 2016 3:32 pm
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am


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