Index on temp table

[iamsonali]

I am really new to Sybase and this has me stumped.

I created a SP and within it, instead of creating a cursor directly, I first created a temporary table like -

create table #tempTrans
(
RecId integer not null,
SeqNo smallint not null,
CustAmount numeric(18,5) null,
CustQuantity numeric(18,5) null
)

Then I select data into the table and declare a cursor on it-

declare trans_cursor cursor for
select RecId, SeqNo, CustAmount, CustQuantity
from #tempTrans

Now, when I execute this SP, I get an error saying - The optimizer could not find a unique index which it could use to scan table '#tempTrans' for cursor 'trans_cursor.

Do temporary tables require indexes? The table that fills the data into the temp table does have 4 indexes on it.

Pls. help!

 

[tinkuK]

Hi,
If there is any primary key for the temporary table, specify it.

like this

create table #tempTrans
(
RecId integer not null primary key,
SeqNo smallint not null,
CustAmount numeric(18,5) null,
CustQuantity numeric(18,5) null
)

while using cursors it is suggested to delcare primary key or explicitly declare unique index on temporary table

Gopal