IBM Mainframe Forum

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.

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.

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

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.

is there any other way to get in order, without using order by clause.

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.

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.

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.