sybperl-l Archive

From: Aloysius A Bauer <aloysius dot bauer at bms dot com>
Subject: Re: Cursurs in Sybperl
Date: Oct 25 2000 6:42PM

Michael Peppler wrote:
> 
> 
> In general using cursors is not such a great idea (IMHO - others may
> disagree :-)
> 
> If you explain what you need to get done we can probably find a way to
> get it done either with cursors or without...
> 

I have a table which has some information "encoded" in one
column. Currently I run a cursur in SQL which takes the encoded
data and extracts the contents into several other columns. The 
SQL to extract is quite long and ugly. but I'll try to explain.
(FYI, The data is from a library catalog, and contains Library of
Congress MARC information). A typical entry looks like
"aNutrition bulletinh[electronic resource]" (without the
quotes). I need to extract the text between a and h (this is
the a subfield). I then insert this into another field in a
"journal_title" table. 
the SQL is listed below (for this post, I edited a GREAT DEAL).
-----------------------------
declare ej_title_cursor cursor
        for select bib#,text245,text856,title,display856,url
        from ej_title_list
        for update of title,display856,url
go
fetch ej_title_cursor into
@bib#,@text,@text856,@newtitle,@new856,@url
if (@@sqlstatus=2)
   begin
        print "nothing in cursor"
        close ej_title_cursor
        deallocate cursor ej_title_cursor
        return
   end
while (@@sqlstatus=0)
   begin
        /* find start pos of 245 subfield a */
        select @start_pos=patindex("%"+char(31)+"a"+"%",@text)
        /* check if start_pos or end_pos=0 */
        /* this occurs when there are no subfields following the
one your lookin
g for */
        if @start_pos>0
           begin
              select @start_pos=@start_pos+2
              select
@end_pos=patindex("%"+char(31)+"%",right(@text,char_length(
@text)-@start_pos))
              if @end_pos=0
                 select @end_pos=char_length(@text)+1
              else
                 select @end_pos=@end_pos+@start_pos
              select
@suba=substring(@text,@start_pos,@end_pos-@start_pos)
           end
/* I edited a bunch of stuff out of here */
/* make updates to table at current cursor location */
        update ej_title_list
         set
title=@suba+@subb+@subn+@subp,url=@sub856u,display856=@suba+@subb+@
subn+@subp+"-"+@sub856i+@sub8563+@sub856p
         where current of ej_title_cursor

Thanks in advance
-- 
Aloysius A. Bauer (bauera@bms.com)
Sr. Information Scientist
Bristol-Myers Squibb