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
 

 

Insertion and Deletion of rows

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

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Wed Jan 23, 2008 4:22 pm    Post subject: Insertion and Deletion of rows
Reply with quote

hi,
can anyone help me in my requirement?

i have a table with a column in which integers are stored in ascending order. whenever a new row is inserted into the table, it reads the maximum value available in the table and adds 1 to it to insert the new row. i need to delete the row with the smallest value in that column with every insertion. its like the GDG versions, when the new one is created the oldest one gets deleted.
Back to top
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Jan 23, 2008 4:27 pm    Post subject:
Reply with quote

Just like you select the max value, you can select the min value. Then just delete it....
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Wed Jan 23, 2008 11:07 pm    Post subject:
Reply with quote

i ve tried it. i can do it in cobol-db2 batch program. however, not getting it done in spufi.
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Thu Jan 24, 2008 11:19 am    Post subject: Hi
Reply with quote

Gautam,

U can achieve this through:

1) SPUFI:

U can run the 2 queries one time.
Before inserting a new row into the table u can run another query to delete the row with the least integer value as you said.

2) TRIGGER:

U can write a trigger to delete the row with the least integer value, when ever user tries to enter a new row into the table.


Correct me If I am wrong...
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Thu Jan 24, 2008 3:09 pm    Post subject:
Reply with quote

thanks for the suggestion. can someone please give me the query?
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Jan 24, 2008 6:42 pm    Post subject:
Reply with quote

DELETE FROM table WHERE intcolumn = min(intcolumn);
INSERT ....
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 962
Location: Richmond, Virginia

PostPosted: Thu Jan 24, 2008 6:50 pm    Post subject:
Reply with quote

Since you can do it in COBOL, you must know how to write the SQL.

Since you are still asking, however, perhaps we can send someone over to type it in for you?
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 To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 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 Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm


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