|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Update Cursor with SortI am in a situation where I need to use an update cursor but also have the
file sorted. The program won't run indicating that it is a read only cursor. Is there a way to get arround this ? What do you mean by file sorted?
-- Show quote"rmcompute" wrote: > I am in a situation where I need to use an update cursor but also have the > file sorted. The program won't run indicating that it is a read only cursor. > Is there a way to get arround this ? The cursor is listed below; note the order by clause. What I was wondering
since it seems you cannot use an order by on an update cursor, is there a system field that uniquely identifies a row ? I could create a read-only cursor, store the unique identifier to a variable, then issue the command Update LocalServiceDataCheck Set CA_SW = 'Y' Where RowID = @thevariable. Does this seem like a viable solution ? Declare cs_CA_Extract Cursor for SELECT Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE, CA_SW, ORDERNUM FROM LocalServiceDataCheck WHERE COMDATE Is Not Null And COMDATE Between @dtStartDate And @dtEndDate AND ENTDATE Is Not Null ORDER BY Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE For Update of CA_SW Show quote "Omnibuzz" wrote: > What do you mean by file sorted? > -- > > > > > "rmcompute" wrote: > > > I am in a situation where I need to use an update cursor but also have the > > file sorted. The program won't run indicating that it is a read only cursor. > > Is there a way to get arround this ? Why are you using a cursor at all? Why can't you just do:
Update LocalServiceDataCheck Set CA_SW = 'Y' WHERE COMDATE Is Not Null And COMDATE Between @dtStartDate And @dtEndDate AND ENTDATE Is Not Null Show quote "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message news:95A86CBF-6213-4B3F-AA8E-1FEA5BC2998D@microsoft.com... > The cursor is listed below; note the order by clause. What I was wondering > since it seems you cannot use an order by on an update cursor, is there a > system field that uniquely identifies a row ? I could create a read-only > cursor, store the unique identifier to a variable, then issue the command > Update LocalServiceDataCheck Set CA_SW = 'Y' > Where RowID = @thevariable. Does this seem like a viable solution ? > > Declare cs_CA_Extract Cursor for > SELECT Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE, CA_SW, ORDERNUM > FROM LocalServiceDataCheck > WHERE COMDATE Is Not Null And COMDATE Between @dtStartDate And @dtEndDate > AND ENTDATE Is Not Null > ORDER BY Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE > For Update of CA_SW > > > "Omnibuzz" wrote: > > > What do you mean by file sorted? > > -- > > > > > > > > > > "rmcompute" wrote: > > > > > I am in a situation where I need to use an update cursor but also have the > > > file sorted. The program won't run indicating that it is a read only cursor. > > > Is there a way to get arround this ? The data is part of an intermediate process over which I have no control of
the data source; the file does not have a unique identifier. Each record represents a service call. The file must be sorted in descending date order by serial number because a process is performed to classify each call based on its prior call. For example, the cursor reads a record of call type 'EMC' It then bumps the pointer to the next record. If this record is the same serial number and it is of a specific all type, some other checks are made against other files and if successful, the PREVIOUS record is classified as Call Back: Serial # Date Call Type Classification 123 04/31/2006 EMC ?? 123 03/30/2006 PM Call Back 123 03/29/2006 PD ?? Show quote "Jim Underwood" wrote: > Why are you using a cursor at all? Why can't you just do: > > Update LocalServiceDataCheck Set CA_SW = 'Y' > WHERE COMDATE Is Not Null > And COMDATE Between @dtStartDate And @dtEndDate > AND ENTDATE Is Not Null > > > "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message > news:95A86CBF-6213-4B3F-AA8E-1FEA5BC2998D@microsoft.com... > > The cursor is listed below; note the order by clause. What I was > wondering > > since it seems you cannot use an order by on an update cursor, is there a > > system field that uniquely identifies a row ? I could create a read-only > > cursor, store the unique identifier to a variable, then issue the command > > Update LocalServiceDataCheck Set CA_SW = 'Y' > > Where RowID = @thevariable. Does this seem like a viable solution ? > > > > Declare cs_CA_Extract Cursor for > > SELECT Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE, CA_SW, ORDERNUM > > FROM LocalServiceDataCheck > > WHERE COMDATE Is Not Null And COMDATE Between @dtStartDate And @dtEndDate > > AND ENTDATE Is Not Null > > ORDER BY Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE > > For Update of CA_SW > > > > > > "Omnibuzz" wrote: > > > > > What do you mean by file sorted? > > > -- > > > > > > > > > > > > > > > "rmcompute" wrote: > > > > > > > I am in a situation where I need to use an update cursor but also have > the > > > > file sorted. The program won't run indicating that it is a read only > cursor. > > > > Is there a way to get arround this ? > > > I see.
So, if I understand correctly, you need this process to lock all of these rows retrieved by the cursor, and process them in a specific order, because the logic for each row is dependent upon one or more previous rows? Show quote "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message news:8EAE7B9A-F585-4CDC-BAD6-FE37B269312F@microsoft.com... > The data is part of an intermediate process over which I have no control of > the data source; the file does not have a unique identifier. Each record > represents a service call. The file must be sorted in descending date order > by serial number because a process is performed to classify each call based > on its prior call. For example, the cursor reads a record of call type 'EMC' > It then bumps the pointer to the next record. If this record is the same > serial number and it is of a specific all type, some other checks are made > against other files and if successful, the PREVIOUS record is classified as > Call Back: > > Serial # Date Call Type Classification > 123 04/31/2006 EMC ?? > 123 03/30/2006 PM Call Back > 123 03/29/2006 PD ?? > > > "Jim Underwood" wrote: > > > Why are you using a cursor at all? Why can't you just do: > > > > Update LocalServiceDataCheck Set CA_SW = 'Y' > > WHERE COMDATE Is Not Null > > And COMDATE Between @dtStartDate And @dtEndDate > > AND ENTDATE Is Not Null > > > > > > "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message > > news:95A86CBF-6213-4B3F-AA8E-1FEA5BC2998D@microsoft.com... > > > The cursor is listed below; note the order by clause. What I was > > wondering > > > since it seems you cannot use an order by on an update cursor, is there a > > > system field that uniquely identifies a row ? I could create a read-only > > > cursor, store the unique identifier to a variable, then issue the command > > > Update LocalServiceDataCheck Set CA_SW = 'Y' > > > Where RowID = @thevariable. Does this seem like a viable solution ? > > > > > > Declare cs_CA_Extract Cursor for > > > SELECT Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE, CA_SW, ORDERNUM > > > FROM LocalServiceDataCheck > > > WHERE COMDATE Is Not Null And COMDATE Between @dtStartDate And @dtEndDate > > > AND ENTDATE Is Not Null > > > ORDER BY Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE > > > For Update of CA_SW > > > > > > > > > "Omnibuzz" wrote: > > > > > > > What do you mean by file sorted? > > > > -- > > > > > > > > > > > > > > > > > > > > "rmcompute" wrote: > > > > > > > > > I am in a situation where I need to use an update cursor but also have > > the > > > > > file sorted. The program won't run indicating that it is a read only > > cursor. > > > > > Is there a way to get arround this ? > > > > > > Yes. The pointer is bumped to the next record and after an analysis of that
record, the pointer may have to be bumped to the previous record to assign it as a call back. I will probably replace the cursor eventually, but under the current time constraint, I just want to get it working. Since it seems that you cannot have an update cursor with the Order by clause, and I will need the table in a specific order, would a read-only cursor work with an update command as I have previously stated that would update the specific record after storing either a system generated row identifier, if such an identifier exists or create my own numeric identifier ? Show quote "Jim Underwood" wrote: > I see. > > So, if I understand correctly, you need this process to lock all of these > rows retrieved by the cursor, and process them in a specific order, because > the logic for each row is dependent upon one or more previous rows? > > "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message > news:8EAE7B9A-F585-4CDC-BAD6-FE37B269312F@microsoft.com... > > The data is part of an intermediate process over which I have no control > of > > the data source; the file does not have a unique identifier. Each record > > represents a service call. The file must be sorted in descending date > order > > by serial number because a process is performed to classify each call > based > > on its prior call. For example, the cursor reads a record of call type > 'EMC' > > It then bumps the pointer to the next record. If this record is the same > > serial number and it is of a specific all type, some other checks are made > > against other files and if successful, the PREVIOUS record is classified > as > > Call Back: > > > > Serial # Date Call Type Classification > > 123 04/31/2006 EMC ?? > > 123 03/30/2006 PM Call Back > > 123 03/29/2006 PD ?? > > > > > > "Jim Underwood" wrote: > > > > > Why are you using a cursor at all? Why can't you just do: > > > > > > Update LocalServiceDataCheck Set CA_SW = 'Y' > > > WHERE COMDATE Is Not Null > > > And COMDATE Between @dtStartDate And @dtEndDate > > > AND ENTDATE Is Not Null > > > > > > > > > "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message > > > news:95A86CBF-6213-4B3F-AA8E-1FEA5BC2998D@microsoft.com... > > > > The cursor is listed below; note the order by clause. What I was > > > wondering > > > > since it seems you cannot use an order by on an update cursor, is > there a > > > > system field that uniquely identifies a row ? I could create a > read-only > > > > cursor, store the unique identifier to a variable, then issue the > command > > > > Update LocalServiceDataCheck Set CA_SW = 'Y' > > > > Where RowID = @thevariable. Does this seem like a viable solution ? > > > > > > > > Declare cs_CA_Extract Cursor for > > > > SELECT Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE, CA_SW, ORDERNUM > > > > FROM LocalServiceDataCheck > > > > WHERE COMDATE Is Not Null And COMDATE Between @dtStartDate And > @dtEndDate > > > > AND ENTDATE Is Not Null > > > > ORDER BY Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE > > > > For Update of CA_SW > > > > > > > > > > > > "Omnibuzz" wrote: > > > > > > > > > What do you mean by file sorted? > > > > > -- > > > > > > > > > > > > > > > > > > > > > > > > > "rmcompute" wrote: > > > > > > > > > > > I am in a situation where I need to use an update cursor but also > have > > > the > > > > > > file sorted. The program won't run indicating that it is a read > only > > > cursor. > > > > > > Is there a way to get arround this ? > > > > > > > > > > > > rmcompute (rmcomp***@discussions.microsoft.com) writes:
> Yes. The pointer is bumped to the next record and after an analysis of There are no hidden identifier you can play with. It was not exactly > that record, the pointer may have to be bumped to the previous record to > assign it as a call back. I will probably replace the cursor > eventually, but under the current time constraint, I just want to get it > working. Since it seems that you cannot have an update cursor with the > Order by clause, and I will need the table in a specific order, would a > read-only cursor work with an update command as I have previously stated > that would update the specific record after storing either a system > generated row identifier, if such an identifier exists or create my own > numeric identifier ? clear from where you got this data, but if the table does not have a unique key, you can always say: ALTER TABLE tbl ADD ident int IDENTITY ALTER TABLE tbl ADD CONSTRAINT u_tbl UNIQUE (ident) and then use ident as the key. Note that it is not likely to be aligned with your ORDER BY criteria. But I have a question. If rows are not unique, and you have ORDER BY Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE there can thus be several rows with the values for these columns. How would that affect your accumulated result? Could you different results depending on which order rows with the same values are visited? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I decided to go with Set-based processing and remove the cursor. What was
taking over 20 minutes to process, now processes in less than 1 minute. I use the row identifier in the non-cursor approach to the problem. Thanks for the help. Show quote "Erland Sommarskog" wrote: > rmcompute (rmcomp***@discussions.microsoft.com) writes: > > > Yes. The pointer is bumped to the next record and after an analysis of > > that record, the pointer may have to be bumped to the previous record to > > assign it as a call back. I will probably replace the cursor > > eventually, but under the current time constraint, I just want to get it > > working. Since it seems that you cannot have an update cursor with the > > Order by clause, and I will need the table in a specific order, would a > > read-only cursor work with an update command as I have previously stated > > that would update the specific record after storing either a system > > generated row identifier, if such an identifier exists or create my own > > numeric identifier ? > > There are no hidden identifier you can play with. It was not exactly > clear from where you got this data, but if the table does not have a > unique key, you can always say: > > ALTER TABLE tbl ADD ident int IDENTITY > ALTER TABLE tbl ADD CONSTRAINT u_tbl UNIQUE (ident) > > and then use ident as the key. Note that it is not likely to be > aligned with your ORDER BY criteria. > > But I have a question. If rows are not unique, and you have > > ORDER BY Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE > > there can thus be several rows with the values for these columns. > How would that affect your accumulated result? Could you different > results depending on which order rows with the same values are > visited? > > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Maybe put everythig into a temp table using rank to order the records, then
loop through the results based on rank? Show quote "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message news:60EBD17B-D9CF-477D-9A9D-51DD04AA3C69@microsoft.com... > Yes. The pointer is bumped to the next record and after an analysis of that > record, the pointer may have to be bumped to the previous record to assign it > as a call back. I will probably replace the cursor eventually, but under the > current time constraint, I just want to get it working. Since it seems that > you cannot have an update cursor with the Order by clause, and I will need > the table in a specific order, would a read-only cursor work with an update > command as I have previously stated that would update the specific record > after storing either a system generated row identifier, if such an identifier > exists or create my own numeric identifier ? > > "Jim Underwood" wrote: > > > I see. > > > > So, if I understand correctly, you need this process to lock all of these > > rows retrieved by the cursor, and process them in a specific order, because > > the logic for each row is dependent upon one or more previous rows? > > > > "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message > > news:8EAE7B9A-F585-4CDC-BAD6-FE37B269312F@microsoft.com... > > > The data is part of an intermediate process over which I have no control > > of > > > the data source; the file does not have a unique identifier. Each record > > > represents a service call. The file must be sorted in descending date > > order > > > by serial number because a process is performed to classify each call > > based > > > on its prior call. For example, the cursor reads a record of call type > > 'EMC' > > > It then bumps the pointer to the next record. If this record is the same > > > serial number and it is of a specific all type, some other checks are made > > > against other files and if successful, the PREVIOUS record is classified > > as > > > Call Back: > > > > > > Serial # Date Call Type Classification > > > 123 04/31/2006 EMC ?? > > > 123 03/30/2006 PM Call Back > > > 123 03/29/2006 PD ?? > > > > > > > > > "Jim Underwood" wrote: > > > > > > > Why are you using a cursor at all? Why can't you just do: > > > > > > > > Update LocalServiceDataCheck Set CA_SW = 'Y' > > > > WHERE COMDATE Is Not Null > > > > And COMDATE Between @dtStartDate And @dtEndDate > > > > AND ENTDATE Is Not Null > > > > > > > > > > > > "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message > > > > news:95A86CBF-6213-4B3F-AA8E-1FEA5BC2998D@microsoft.com... > > > > > The cursor is listed below; note the order by clause. What I was > > > > wondering > > > > > since it seems you cannot use an order by on an update cursor, is > > there a > > > > > system field that uniquely identifies a row ? I could create a > > read-only > > > > > cursor, store the unique identifier to a variable, then issue the > > command > > > > > Update LocalServiceDataCheck Set CA_SW = 'Y' > > > > > Where RowID = @thevariable. Does this seem like a viable solution ? > > > > > > > > > > Declare cs_CA_Extract Cursor for > > > > > SELECT Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE, CA_SW, ORDERNUM > > > > > FROM LocalServiceDataCheck > > > > > WHERE COMDATE Is Not Null And COMDATE Between @dtStartDate And > > @dtEndDate > > > > > AND ENTDATE Is Not Null > > > > > ORDER BY Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE > > > > > For Update of CA_SW > > > > > > > > > > > > > > > "Omnibuzz" wrote: > > > > > > > > > > > What do you mean by file sorted? > > > > > > -- > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "rmcompute" wrote: > > > > > > > > > > > > > I am in a situation where I need to use an update cursor but also > > have > > > > the > > > > > > > file sorted. The program won't run indicating that it is a read > > only > > > > cursor. > > > > > > > Is there a way to get arround this ? > > > > > > > > > > > > > > > > > > Thank you
Show quote "Jim Underwood" wrote: > Maybe put everythig into a temp table using rank to order the records, then > loop through the results based on rank? > "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message > news:60EBD17B-D9CF-477D-9A9D-51DD04AA3C69@microsoft.com... > > Yes. The pointer is bumped to the next record and after an analysis of > that > > record, the pointer may have to be bumped to the previous record to assign > it > > as a call back. I will probably replace the cursor eventually, but under > the > > current time constraint, I just want to get it working. Since it seems > that > > you cannot have an update cursor with the Order by clause, and I will need > > the table in a specific order, would a read-only cursor work with an > update > > command as I have previously stated that would update the specific record > > after storing either a system generated row identifier, if such an > identifier > > exists or create my own numeric identifier ? > > > > "Jim Underwood" wrote: > > > > > I see. > > > > > > So, if I understand correctly, you need this process to lock all of > these > > > rows retrieved by the cursor, and process them in a specific order, > because > > > the logic for each row is dependent upon one or more previous rows? > > > > > > "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message > > > news:8EAE7B9A-F585-4CDC-BAD6-FE37B269312F@microsoft.com... > > > > The data is part of an intermediate process over which I have no > control > > > of > > > > the data source; the file does not have a unique identifier. Each > record > > > > represents a service call. The file must be sorted in descending date > > > order > > > > by serial number because a process is performed to classify each call > > > based > > > > on its prior call. For example, the cursor reads a record of call type > > > 'EMC' > > > > It then bumps the pointer to the next record. If this record is the > same > > > > serial number and it is of a specific all type, some other checks are > made > > > > against other files and if successful, the PREVIOUS record is > classified > > > as > > > > Call Back: > > > > > > > > Serial # Date Call Type Classification > > > > 123 04/31/2006 EMC ?? > > > > 123 03/30/2006 PM Call Back > > > > 123 03/29/2006 PD ?? > > > > > > > > > > > > "Jim Underwood" wrote: > > > > > > > > > Why are you using a cursor at all? Why can't you just do: > > > > > > > > > > Update LocalServiceDataCheck Set CA_SW = 'Y' > > > > > WHERE COMDATE Is Not Null > > > > > And COMDATE Between @dtStartDate And @dtEndDate > > > > > AND ENTDATE Is Not Null > > > > > > > > > > > > > > > "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message > > > > > news:95A86CBF-6213-4B3F-AA8E-1FEA5BC2998D@microsoft.com... > > > > > > The cursor is listed below; note the order by clause. What I was > > > > > wondering > > > > > > since it seems you cannot use an order by on an update cursor, is > > > there a > > > > > > system field that uniquely identifies a row ? I could create a > > > read-only > > > > > > cursor, store the unique identifier to a variable, then issue the > > > command > > > > > > Update LocalServiceDataCheck Set CA_SW = 'Y' > > > > > > Where RowID = @thevariable. Does this seem like a viable solution > ? > > > > > > > > > > > > Declare cs_CA_Extract Cursor for > > > > > > SELECT Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE, CA_SW, > ORDERNUM > > > > > > FROM LocalServiceDataCheck > > > > > > WHERE COMDATE Is Not Null And COMDATE Between @dtStartDate And > > > @dtEndDate > > > > > > AND ENTDATE Is Not Null > > > > > > ORDER BY Branch, SERIALNUM, COMDATE, ENTDATE, WOTYPE > > > > > > For Update of CA_SW > > > > > > > > > > > > > > > > > > "Omnibuzz" wrote: > > > > > > > > > > > > > What do you mean by file sorted? > > > > > > > -- > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "rmcompute" wrote: > > > > > > > > > > > > > > > I am in a situation where I need to use an update cursor but > also > > > have > > > > > the > > > > > > > > file sorted. The program won't run indicating that it is a > read > > > only > > > > > cursor. > > > > > > > > Is there a way to get arround this ? > > > > > > > > > > > > > > > > > > > > > > > > > > >  |
|||||||||||||||||||||||
Other interesting topics