Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1244 > unrolled thread

SQL Cursors

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  SQL Cursors simisa@tiscalinet.it (simisa) - 2012-09-04 14:29 +0200
    Re: SQL Cursors "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-09-04 11:27 -0400
      Re: SQL Cursors sim <simisa@tiscalinet.it> - 2012-09-05 08:32 +0200
        Re: SQL Cursors "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-05 06:18 -0400
          Re: SQL Cursors sim <simisa@tiscalinet.it> - 2012-09-05 13:00 +0200
            Re: SQL Cursors "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-05 07:45 -0400
              Re: SQL Cursors sim <simisa@tiscalinet.it> - 2012-09-05 18:19 +0200
              Re: SQL Cursors rja.carnegie@gmail.com - 2012-09-05 15:36 -0700
                Re: SQL Cursors "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-05 18:44 -0400
                  Re: SQL Cursors rja.carnegie@gmail.com - 2012-09-05 16:58 -0700
                  Re: SQL Cursors Gene Wirchenko <genew@ocis.net> - 2012-09-05 17:23 -0700
                    Re: SQL Cursors rja.carnegie@gmail.com - 2012-09-06 02:50 -0700
                Re: SQL Cursors "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-05 18:44 -0400
                  Re: SQL Cursors sim <simisa@tiscalinet.it> - 2012-09-06 09:37 +0200

#1244 — SQL Cursors

Hallo all,
I have a problem to resolv asap...I hope that somebody can help me

1 Table called classage

agent1     10000   value2     1
agent2     8000     value2     2
agent3     7500     value2     3
agent4     1000     value2     4
...

The table is order by rank desc.
I need to update the value2 like this:

agent1     10000   0             1
agent2     8000     2000       2
agent3     7500     500         3
agent4     1000     6500       4


Is it possible ?

thanks in advance

-- 


questo articolo e` stato inviato via web dal servizio gratuito 
http://www.newsland.it/news segnala gli abusi ad abuse@newsland.it

[toc] | [next] | [standalone]

#1246

simisa wrote:
> Hallo all,
> I have a problem to resolv asap...I hope that somebody can help me
>
> 1 Table called classage
>
> agent1     10000   value2     1
> agent2     8000     value2     2
> agent3     7500     value2     3
> agent4     1000     value2     4
> ...
>
> The table is order by rank desc.
> I need to update the value2 like this:
>
> agent1     10000   0             1
> agent2     8000     2000       2
> agent3     7500     500         3
> agent4     1000     6500       4
>
>
> Is it possible ?
>
Yes, it is certainly possible. Without cursors.

What version of sql server (this is very relevant for this question)?
What are the column names and datatypes? What is the primary key of the
table? You can best answer the latter two questions by using SSMS (or EM,
depending on your sql version) to generate the table-creation DDL script for
the table and posting it.

[toc] | [prev] | [next] | [standalone]

#1248

On 2012-09-04 15:27:18 +0000, Bob Barrows said:

> simisa wrote:
>> Hallo all,
>> I have a problem to resolv asap...I hope that somebody can help me
>> 
>> 1 Table called classage
>> 
>> agent1     10000   value2     1
>> agent2     8000     value2     2
>> agent3     7500     value2     3
>> agent4     1000     value2     4
>> ...
>> 
>> The table is order by rank desc.
>> I need to update the value2 like this:
>> 
>> agent1     10000   0             1
>> agent2     8000     2000       2
>> agent3     7500     500         3
>> agent4     1000     6500       4
>> 
>> 
>> Is it possible ?
>> 
> Yes, it is certainly possible. Without cursors.
> 
> What version of sql server (this is very relevant for this question)?
> What are the column names and datatypes? What is the primary key of the
> table? You can best answer the latter two questions by using SSMS (or EM,
> depending on your sql version) to generate the table-creation DDL script for
> the table and posting it.

Hallo,
here we are:

Table CLASSAGE:

agents	varchar(50)	Checked
netto		decimal(38, 2)	Checked
netto2	decimal(38, 2)	Checked
num		int	Checked


where num is the ranking and netto2 is the calculated value to obtain.
No ID/Identity.

Thank you in advance.






--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#1249

sim wrote:
> On 2012-09-04 15:27:18 +0000, Bob Barrows said:
>
>> simisa wrote:
>>> Hallo all,
>>> I have a problem to resolv asap...I hope that somebody can help me
>>>
>>> 1 Table called classage
>>>
>>> agent1     10000   value2     1
>>> agent2     8000     value2     2
>>> agent3     7500     value2     3
>>> agent4     1000     value2     4
>>> ...
>>>
>>> The table is order by rank desc.
>>> I need to update the value2 like this:
>>>
>>> agent1     10000   0             1
>>> agent2     8000     2000       2
>>> agent3     7500     500         3
>>> agent4     1000     6500       4
>>>
>>>
>>> Is it possible ?
>>>
>> Yes, it is certainly possible. Without cursors.
>>
>> What version of sql server (this is very relevant for this question)?
>> What are the column names and datatypes? What is the primary key of
>> the table? You can best answer the latter two questions by using
>> SSMS (or EM, depending on your sql version) to generate the
>> table-creation DDL script for the table and posting it.
>
> Hallo,
> here we are:
>
> Table CLASSAGE:
>
> agents varchar(50) Checked
> netto decimal(38, 2) Checked
> netto2 decimal(38, 2) Checked
> num int Checked
>
>
> where num is the ranking and netto2 is the calculated value to obtain.
> No ID/Identity.
>

You failed to answer my very first question!
What version of sql server are you using? I really do need to know this! 

[toc] | [prev] | [next] | [standalone]

#1250

On 2012-09-05 10:18:34 +0000, Bob Barrows said:

> sim wrote:
>> On 2012-09-04 15:27:18 +0000, Bob Barrows said:
>> 
>>> simisa wrote:
>>>> Hallo all,
>>>> I have a problem to resolv asap...I hope that somebody can help me
>>>> 
>>>> 1 Table called classage
>>>> 
>>>> agent1     10000   value2     1
>>>> agent2     8000     value2     2
>>>> agent3     7500     value2     3
>>>> agent4     1000     value2     4
>>>> ...
>>>> 
>>>> The table is order by rank desc.
>>>> I need to update the value2 like this:
>>>> 
>>>> agent1     10000   0             1
>>>> agent2     8000     2000       2
>>>> agent3     7500     500         3
>>>> agent4     1000     6500       4
>>>> 
>>>> 
>>>> Is it possible ?
>>>> 
>>> Yes, it is certainly possible. Without cursors.
>>> 
>>> What version of sql server (this is very relevant for this question)?
>>> What are the column names and datatypes? What is the primary key of
>>> the table? You can best answer the latter two questions by using
>>> SSMS (or EM, depending on your sql version) to generate the
>>> table-creation DDL script for the table and posting it.
>> 
>> Hallo,
>> here we are:
>> 
>> Table CLASSAGE:
>> 
>> agents varchar(50) Checked
>> netto decimal(38, 2) Checked
>> netto2 decimal(38, 2) Checked
>> num int Checked
>> 
>> 
>> where num is the ranking and netto2 is the calculated value to obtain.
>> No ID/Identity.
>> 
> 
> You failed to answer my very first question!
> What version of sql server are you using? I really do need to know this!

Sorry !!! it's a sql2000 sp3

I work with DTS.

I have the possibility to work on sql2008r2, not a problem.

Thanks


--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#1251

sim wrote:
> On 2012-09-05 10:18:34 +0000, Bob Barrows said:
>
>> sim wrote:
>>> On 2012-09-04 15:27:18 +0000, Bob Barrows said:
>>>
>>>> simisa wrote:
>>>>> Hallo all,
>>>>> I have a problem to resolv asap...I hope that somebody can help me
>>>>>
>>>>> 1 Table called classage
>>>>>
>>>>> agent1     10000   value2     1
>>>>> agent2     8000     value2     2
>>>>> agent3     7500     value2     3
>>>>> agent4     1000     value2     4
>>>>> ...
>>>>>
>>>>> The table is order by rank desc.
>>>>> I need to update the value2 like this:
>>>>>
>>>>> agent1     10000   0             1
>>>>> agent2     8000     2000       2
>>>>> agent3     7500     500         3
>>>>> agent4     1000     6500       4
>>>>>
>>>>>
>>>>> Is it possible ?
>>>>>
>>>> Yes, it is certainly possible. Without cursors.
>>>>
>>>> What version of sql server (this is very relevant for this
>>>> question)? What are the column names and datatypes? What is the
>>>> primary key of the table? You can best answer the latter two
>>>> questions by using SSMS (or EM, depending on your sql version) to
>>>> generate the table-creation DDL script for the table and posting
>>>> it.
>>>
>>> Hallo,
>>> here we are:
>>>
>>> Table CLASSAGE:
>>>
>>> agents varchar(50) Checked
>>> netto decimal(38, 2) Checked
>>> netto2 decimal(38, 2) Checked
>>> num int Checked
>>>
>>>
>>> where num is the ranking and netto2 is the calculated value to
>>> obtain. No ID/Identity.
>>>
>>
>> You failed to answer my very first question!
>> What version of sql server are you using? I really do need to know
>> this!
>
> Sorry !!! it's a sql2000 sp3
>
> I work with DTS.
>
> I have the possibility to work on sql2008r2, not a problem.
>
With 2008, a CTE can  be used. This is not the case in 2000, but the 
solutions are very similar. It involves a self-join:

update c
set netto2 = coalesce(q.netto,c.netto) - c.netto
from classage as c join
(select netto,num from classage) as q
on c.num = q.num + 1

It is not recommended to store calculated values this way. This value can be 
generated on the fly using a similar select statement. My advice would be TO 
DROP that netto2 column from CLASSAGE and create a view to calculate it:

create view vCLASSAGE AS
SELECT agents
, c.netto
, coalesce(q.netto,c.netto) - c.netto
, c.num
from classage as c join
(select netto,num from classage) as q
on c.num = q.num + 1

That way it will always be correct and you will never have to run extra code 
to update that column. 

[toc] | [prev] | [next] | [standalone]

#1252

On 2012-09-05 11:45:31 +0000, Bob Barrows said:

> update c
> set netto2 = coalesce(q.netto,c.netto) - c.netto
> from classage as c join
> (select netto,num from classage) as q
> on c.num = q.num + 1

Absolutely fantastic ! Goal ! ! !
Thank you so much !

it is correct.


--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#1253

Of course you assumed that the ranking column [num] runs 1, 2, 3, 4, 5, ...
with no numbers missing or duplicated.  Otherwise, reviving the question
of a cursor looks good to me......

I forget whether calculating a rank by Transact-SQL first appeared in 
edition 2005 - and whether those same issues come up.  But if the data 
is historic and not changing, then performing the calculation while 
a user is waiting may be not best.

An advantage of recommending a solution without a cursor is that if
the question is someone's assignment at college, then you aren't
being helpful.  :-)

[toc] | [prev] | [next] | [standalone]

#1255

rja.carnegie@gmail.com wrote:
> Of course you assumed that the ranking column [num] runs 1, 2, 3, 4,
> 5, ... with no numbers missing or duplicated.

I could only go by the data offered.

>  Otherwise, reviving
> the question of a cursor looks good to me......

Why? What would it solve, and how?

>
> I forget whether calculating a rank by Transact-SQL first appeared in
> edition 2005 - and whether those same issues come up.  But if the data
> is historic and not changing, then performing the calculation while
> a user is waiting may be not best.

YMMV

>
> An advantage of recommending a solution without a cursor is that if
> the question is someone's assignment at college, then you aren't
> being helpful.  :-)

That assumes I want to be helpful to someone doing their homework 

[toc] | [prev] | [next] | [standalone]

#1258

On Wednesday, September 5, 2012 11:59:01 PM UTC+1, Bob Barrows wrote:
> rja.carnegie@gmail.com wrote:
> 
> > Of course you assumed that the ranking column [num] runs 1, 2, 3, 4,
> > 5, ... with no numbers missing or duplicated.
> 
> I could only go by the data offered.

Yes.  I continue to say that ranking can get funny.

> >  Otherwise, reviving
> > the question of a cursor looks good to me......
> 
> Why? What would it solve, and how?

You could run throu‰gh the table, storing the value from each row in a
variable and using it to calculate the new value to set in the next row.
Some catches in ranking can be easily avoided, and the programmer 
who asked the question apparently is cursor-minded.  That's usually 
seen as a lack, but for now, they would be getting a program design
that they understand instead of a mysterious magic spell.  ;-)

Whatever works and the language allows - after that, you're just 
putting on polish.  (Although there are reasons, such as having a 
program be clear, modifiable, extendable, acceptably fast...)

[toc] | [prev] | [next] | [standalone]

#1259

On Wed, 5 Sep 2012 18:44:22 -0400, "Bob Barrows"
<reb01501@NOSPAMyahoo.com> wrote:

>rja.carnegie@gmail.com wrote:

[snip]

>> An advantage of recommending a solution without a cursor is that if
>> the question is someone's assignment at college, then you aren't
>> being helpful.  :-)

     Why would cursor or not have anything to do with whether the
question is assignment-related?

     (Yes, I can generally smell a homework question quite well.  (It
is easy; they reek of it.)  I am asking for the general case.)

>That assumes I want to be helpful to someone doing their homework 

     Which is probably the case, actually.  You just do not want to do
the homework for him.  I understand that some people have gone for the
sabotage approach; that is too much effort for me.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#1261

On Thursday, September 6, 2012 1:22:59 AM UTC+1, Gene Wirchenko wrote:
> On Wed, 5 Sep 2012 18:44:22 -0400, "Bob Barrows" 
> <reb01501@NOSPAMyahoo.com> wrote: 
> 
> >rja.ca...@gmail.com wrote: 
> 
> >> An advantage of recommending a solution without a cursor is that if 
> >> the question is someone's assignment at college, then you aren't 
> >> being helpful.  :-) 
> 
> 
>      Why would cursor or not have anything to do with whether the 
> question is assignment-related? 
> 
>      (Yes, I can generally smell a homework question quite well.  (It 
> is easy; they reek of it.)  I am asking for the general case.) 

It's only in this case, where the request title is "SQL Cursors".
We could infer that this week's college assignment is called 
"SQL Cursors", and a program solution that doesn't use a cursor 
will not get credit!  ;-)

Cursors accommodate the way that some of us thought about databases 
before we learned about SQL, and it may be generally better to think
harder about a problem and then use a non-cursor solution.  But they
are one of the tools available to you, and a student should learn
how to use them.  If I catch myself designing a cursor-like 
implementation without using the word, then giving in and doing it
explicitly is one good move.

The extensive variations in Microsoft Transact-SQL syntax for cursors
implies to me that the ANSI SQL cursor specification is of limited
capability, although sometimes I think Microsoft just likes to create
the impression.  This also means that your program code and your 
skill set aren't particularly portable to other SQL implementations, although not everyone will worry about that.  
<http://en.wikipedia.org/wiki/Cursor_%28databases%29#Disadvantages_of_cursors>
says, "Microsoft SQL Server implements cursors by creating a temporary
table and populating it with the query's result-set. If a cursor is not
properly closed (deallocated), the resources will not be freed until 
the SQL session (connection) itself is closed."  ...Temporary tables?

Oh, yes - there's all the fuss of defining and then opening the cursor,
and testing the result each time you read it, and duplicating the FETCH
statement both before the WHILE loop and inside it.  I like to use 
a Microsoft cursor variable instead of a cursor name, because some of
that goes away; in particular, a cursor reliably disappears (I trust)
when any variables that are holding that cursor go out of scope, 
and you don't have to write error-handling to deal with a cursor 
whose state is undetermined.

For when you do, some of my stored procedures from SQL Server 2000 
handle an error with "GOTO failure", and part of the end of the 
program looks like this - you do need to edit the cursor name in:

GOTO terminate

failure:

/* This code deals with a named cursor which may or may not exist. */

IF ( CURSOR_STATUS('global', 'CursorName') >= 0  /* cursor is open */
        OR CURSOR_STATUS('local', 'CursorName') >= 0 )
    CLOSE CursorName
IF ( CURSOR_STATUS('global','CursorName') = -1  /* cursor is closed */
        OR CURSOR_STATUS('local','CursorName') = -1 )
    DEALLOCATE CursorName

-- also deal with temporary table, transaction, RAISERROR to parent procedure

terminate:  /* end of procedure */

[toc] | [prev] | [next] | [standalone]

#1256

rja.carnegie@gmail.com wrote:
> Of course you assumed that the ranking column [num] runs 1, 2, 3, 4,
> 5, ... with no numbers missing or duplicated.

I could only go by the data offered.

>  Otherwise, reviving
> the question of a cursor looks good to me......

Why? What would it solve, and how?

>
> I forget whether calculating a rank by Transact-SQL first appeared in
> edition 2005 - and whether those same issues come up.  But if the data
> is historic and not changing, then performing the calculation while
> a user is waiting may be not best.

YMMV

>
> An advantage of recommending a solution without a cursor is that if
> the question is someone's assignment at college, then you aren't
> being helpful.  :-)

That assumes I want to be helpful to someone doing their homework 

[toc] | [prev] | [next] | [standalone]

#1260

On 2012-09-05 22:44:22 +0000, Bob Barrows said:

> rja.carnegie@gmail.com wrote:
>> Of course you assumed that the ranking column [num] runs 1, 2, 3, 4,
>> 5, ... with no numbers missing or duplicated.
> 
> I could only go by the data offered.
> 
>> Otherwise, reviving
>> the question of a cursor looks good to me......
> 
> Why? What would it solve, and how?
> 
>> 
>> I forget whether calculating a rank by Transact-SQL first appeared in
>> edition 2005 - and whether those same issues come up.  But if the data
>> is historic and not changing, then performing the calculation while
>> a user is waiting may be not best.
> 
> YMMV
> 
>> 
>> An advantage of recommending a solution without a cursor is that if
>> the question is someone's assignment at college, then you aren't
>> being helpful.  :-)
> 
> That assumes I want to be helpful to someone doing their homework

I used the rank from DTS in field NUM…the solution from Bob for me it's 
perfect !

Now i have the Agent's Hit Parade :-) with delta value from one agent 
to the top 1 and a ranked list.

Very happy, thanks again.


--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web