FETCHING ROWS FROM ONE TABLE TO OTHER TABLE USING CURSORS



IBM's flagship relational database management system

FETCHING ROWS FROM ONE TABLE TO OTHER TABLE USING CURSORS

Postby avvarycobol » Wed Mar 23, 2011 8:05 pm

HI ALL.

I USED CURSORS TO FETCH ROWS FROM ONE TABLE AND INSERTING THOSE ROWS INTO OTHER TABLE. MY INPUT TABLE COLUMN EMPNO IS IN ASCENDING ORDER, BUT THE OUTPUT OF THAT IS NOT IN ASCENDING ORDER. WHAT IS THE CAUSE FOR THIS. IN INPUT TABLE I DIDN'T USED PRIMARY KEY OR INDEXES.
avvarycobol
 
Posts: 5
Joined: Mon Mar 07, 2011 5:28 pm
Has thanked: 0 time
Been thanked: 0 time

Re: FETCHING ROWS FROM ONE TABLE TO OTHER TABLE USING CURSOR

Postby BillyBoyo » Wed Mar 23, 2011 8:15 pm

Don't shout, please (using CAPS ALL THE TIME).

We'd need lots more information to help directly. Are you asking why the first table is in empno order? We have no way of knowing. Likewise for output table. You'll have to look at your definitions and data.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: FETCHING ROWS FROM ONE TABLE TO OTHER TABLE USING CURSOR

Postby avvarycobol » Wed Mar 23, 2011 8:20 pm

this is my program.

declare cur1 cursor for select * from table1
open cur1

fetch cur1 into host variables
insert into table2 values host variables

and closing all the cursors

here fetching was done until end of table

my input table is :

empno empname
1 bill
2 clinton
3 hillary
4 a
5 b
6 c
7 d
8 e
9 f

my output table is :

empno empname
5 b
6 c
7 d
8 e
9 f
1 bill
2 clinton
3 hillary
4 a
avvarycobol
 
Posts: 5
Joined: Mon Mar 07, 2011 5:28 pm
Has thanked: 0 time
Been thanked: 0 time

Re: FETCHING ROWS FROM ONE TABLE TO OTHER TABLE USING CURSOR

Postby NicC » Thu Mar 24, 2011 1:17 am

There is no defined order for the retrieval of rows - you can issue the same select and get different sequences back - unless you use ORDER.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: FETCHING ROWS FROM ONE TABLE TO OTHER TABLE USING CURSOR

Postby avvarycobol » Thu Mar 24, 2011 1:03 pm

is there any other way to get in order, without using order by clause.
avvarycobol
 
Posts: 5
Joined: Mon Mar 07, 2011 5:28 pm
Has thanked: 0 time
Been thanked: 0 time

Re: FETCHING ROWS FROM ONE TABLE TO OTHER TABLE USING CURSOR

Postby BillyBoyo » Thu Mar 24, 2011 1:33 pm

Try using a manual, or searching the internet, or asking around a bit at your end. I'm not trying to cut you off, it's just that "reading the manual" is such a useful first step, that you have to get used to it. When looking for something, you'll find other things, or your'll sometimes find "why" it is like that, and then maybe that'll make you think of something useful somewhere else.

I can't say IBM manuals are always the greatest, so if there is something you have read, and thought about and tried to check out, and still don't fully understand, come back here and explain.

If we just spoon-feed you answers to really simple questions, you'll not learn anything. Doesn't help any of us.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: FETCHING ROWS FROM ONE TABLE TO OTHER TABLE USING CURSOR

Postby NicC » Thu Mar 24, 2011 4:22 pm

You could add a column called, for example, SEQUENCE. This would hold a 'row number'. When you add a row you check the highest existing sequence number, add one, and insert your row. In some flavours of relational databases there is a automatic row_id that you can use.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: FETCHING ROWS FROM ONE TABLE TO OTHER TABLE USING CURSOR

Postby The Butcher » Sun Mar 27, 2011 8:36 pm

Hello
There are useful information missing. Information of all indics of the input table and output table are missing. How are they defined? Any clustering preferences of these indics may affect the result SELECT ordering. Just my 2 pennies.
The Butcher
 
Posts: 3
Joined: Thu Mar 17, 2011 6:52 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post