Hassle with order by in updateable cursors

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Hassle with order by in updateable cursors

Author

Topic

RobMarsh
Starting Member

28 Posts

Posted - 2002-03-07 : 15:39:44

I'm trying to get this cursor to work..

declare AllRecords cursor for
select cust_code, ncc_code from rtsubase order by cust_code,
ncc_code
for update

when I run the procedure, all I get is:

'The cursor is READ ONLY.'

If I remove the order by, it works fine. Any Ideas on how to keep the order by and still have an updateable cursor?

Rob

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-07 : 15:48:30

Well, why use a cursor? What else does your code do? Post your code and we'll take a stab at a non-cursor solution...trust us on this, it can almost certainly be done and will be faster too.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-07 : 15:51:07

Rob People is correct, a set-based solution can almost always be found which is faster ( and easier ).

RobMarsh
Starting Member

28 Posts

Posted - 2002-03-08 : 08:55:08

Well, I'm trying to get a set of data out of a large text file into a table.

I've imported it fine into a temporary table and sorted out column sizes and so forth.
The data in the table needs to be grouped by one field. Once that is done,
I only want the first row in each grouping to remain in the table.

For example:

field used
10477830031302117 041001 01 2 031302117 keep this
10484800031302117 041001 01 2 031302117 }
10491400031302117 041001 01 2 031302117 } zap these
10496460031302117 041001 01 2 031302117 }
10497520031302117 041001 01 2 031302117 }
10665439031302269 091301 01 2 031302269 keep this
10835018031302269 091301 01 2 031302269 zap this

If there is a way to do it in one statement, that would be great.

Thanks,

Rob

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-08 : 09:36:05

add an identity column to your temp table, and then delete from the temp table where the id != min(id) for each key column ( it appears in this case it is the last column if i follow your example ).

in particular, using the five columns you had below, let's name them a,b,c,d,e and the table #temp:

delete #temp
from #temp t
where exists (
select 1
from #temp
group by e
having e = t.e and t.id != min(id) )

Jay99

468 Posts

Posted - 2002-03-08 : 09:37:05



create table ncc_code (
cust_code varchar(50),
ncc_code varchar(50)
)

insert ncc_code values('10477830031302117','041001')
insert ncc_code values('10484800031302117','041001')
insert ncc_code values('10491400031302117','041001')
insert ncc_code values('10496460031302117','041001')
insert ncc_code values('10497520031302117','041001')
insert ncc_code values('10665439031302269','091301')
insert ncc_code values('10835018031302269','091301')
go

create table #temptable (
    counter int identity(1,1),
    cust_code varchar(50),
    ncc_code varchar(50)
)

insert #temptable
select
    cust_code,
    ncc_code
from
    ncc_code
go



select
    cust_code,
    ncc_code
from
    #temptable t
where not exists (
    select 1
    from
        #temptable
    where
        t.ncc_code = ncc_code and
        t.counter > counter)

...would you like fries with that?

Jay

EDIT: setbasedisthetruepath, what?!? are you like my evil twin? It's almost as if we share one brain... (except you always post while I am still typing . . .

)

Edited by - Jay99 on 03/08/2002 09:40:19

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-08 : 11:32:21

How about:

1. Import the data into a temp "holding" table like Jay suggests;
2. INSERT into the final table using the following:

INSERT INTO finalTable SELECT DISTINCT * FROM holdingTable

The DISTINCT removes duplicates automatically. The holding table needs to have the exact structure as the destination table for this to work.

Actually, here's Jay's code with the modification:

create table ncc_code (cust_code varchar(50), ncc_code varchar(50))
insert ncc_code values('10477830031302117','041001')
insert ncc_code values('10484800031302117','041001')
insert ncc_code values('10491400031302117','041001')
insert ncc_code values('10496460031302117','041001')
insert ncc_code values('10497520031302117','041001')
insert ncc_code values('10665439031302269','091301')
insert ncc_code values('10835018031302269','091301')
go
create table #temptable ( cust_code varchar(50), ncc_code varchar(50))
insert #temptable select DISTINCT cust_code, ncc_code from ncc_code
go

This is reversed from what I said earlier, in that the temp table has the distinct values, and the original table the duplicates.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-08 : 11:36:27

The only problem is there aren't any duplicates. From his example, he wants to discard all rows after the first one based on the last column. he's already got the data in the temp table, so the delete i suggested ought to do it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-08 : 11:50:37

Didn't see that. Must...get...more...sleep...

RobMarsh
Starting Member

28 Posts

Posted - 2002-03-08 : 13:02:07

Thanks for all the suggestions, guys.

I now have a nice tidy import file.

Milk and cookies awarded to all of you.

Rob

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources