Can we do a double Cursor?

Can we do a double Cursor where we select the data form one table using a
cursor and then use the key data such as ORDER number to select data from
another table using a cursor?
regards,
RonYou can. You can also try to take your bike on the freeway at rush hour and
challenge a street racer...
Any reason you can't do this with a simple join? What exactly are you
trying to accomplish?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
<Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
> Can we do a double Cursor where we select the data form one table using a
> cursor and then use the key data such as ORDER number to select data from
> another table using a cursor?
> --
> regards,
> Ron|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I have absolutely no idea what an "SQR program" is. Do you mean square
root?
Anyway, a double cursor would look like this:
CREATE TABLE #pk
(
id INT PRIMARY KEY
)
CREATE TABLE #fk
(
id int FOREIGN KEY REFERENCES #pk(id),
foo VARCHAR(2)
)
SET NOCOUNT ON
INSERT #pk
SELECT 1
UNION SELECT 2
INSERT #fk
SELECT 1, 'a'
UNION SELECT 1, 'b'
UNION SELECT 2, 'c'
DECLARE @.pkID INT, @.foo VARCHAR(2)
DECLARE c1 CURSOR FOR
SELECT id FROM #pk
OPEN c1
FETCH NEXT FROM c1 INTO @.pkID
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE c2 CURSOR FOR
SELECT foo FROM #fk WHERE id = @.pkID
OPEN c2
FETCH NEXT FROM c2 INTO @.foo
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Outer loop: '+RTRIM(@.pkID)
PRINT 'Inner loop: '+@.foo
FETCH NEXT FROM c2 INTO @.foo
END
CLOSE c2
DEALLOCATE c2
FETCH NEXT FROM c1 INTO @.pkID
END
CLOSE c1
DEALLOCATE c1
DROP TABLE #fk, #pk
Please don't do this on a production system. If you do, don't tell them I
told you how to do it. I will deny it and tell them it was Celko, spoofing
my IP and all.
On 3/22/05 8:03 PM, in article
9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
<Ronhayim@.discussions.microsoft.com> wrote:

> Im trying to simulate SQR program. Do you have an example where you use
> double cursor.|||On Tue, 22 Mar 2005 15:41:02 -0800, Ron wrote:

>Can we do a double Cursor where we select the data form one table using a
>cursor and then use the key data such as ORDER number to select data from
>another table using a cursor?
Hi Ron,
Even if you really do need a cursor (which I doubt - and the statistics
are on my side), there is really no need to use two of the beasts.
Why not create one query that joins the two tables the way you want them
to be joined, filters rows you don't need and returns only the columns
you need? Then, if you really must, you can always use that query for
your cursor...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The script was a lot of help. SQR is a program that used by Peoplesoft.
This was just a special need. All of you right, just joining the tables
should do the it.
"Aaron [SQL Server MVP]" wrote:

> I have absolutely no idea what an "SQR program" is. Do you mean square
> root?
> Anyway, a double cursor would look like this:
>
> CREATE TABLE #pk
> (
> id INT PRIMARY KEY
> )
> CREATE TABLE #fk
> (
> id int FOREIGN KEY REFERENCES #pk(id),
> foo VARCHAR(2)
> )
> SET NOCOUNT ON
> INSERT #pk
> SELECT 1
> UNION SELECT 2
> INSERT #fk
> SELECT 1, 'a'
> UNION SELECT 1, 'b'
> UNION SELECT 2, 'c'
> DECLARE @.pkID INT, @.foo VARCHAR(2)
> DECLARE c1 CURSOR FOR
> SELECT id FROM #pk
> OPEN c1
> FETCH NEXT FROM c1 INTO @.pkID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DECLARE c2 CURSOR FOR
> SELECT foo FROM #fk WHERE id = @.pkID
> OPEN c2
> FETCH NEXT FROM c2 INTO @.foo
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Outer loop: '+RTRIM(@.pkID)
> PRINT 'Inner loop: '+@.foo
> FETCH NEXT FROM c2 INTO @.foo
> END
> CLOSE c2
> DEALLOCATE c2
> FETCH NEXT FROM c1 INTO @.pkID
> END
> CLOSE c1
> DEALLOCATE c1
> DROP TABLE #fk, #pk
>
> Please don't do this on a production system. If you do, don't tell them I
> told you how to do it. I will deny it and tell them it was Celko, spoofin
g
> my IP and all.
>
> On 3/22/05 8:03 PM, in article
> 9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
> <Ronhayim@.discussions.microsoft.com> wrote:
>
>

Can we add tooltip to the Reports available in Report Manager?

Hi,
In my Report Manager window, I have created the Folder "Test" and under this folder I have uploaded 2 .rdl file. When I am placing the cursor over the Report Items, it shows 'Items in Test' instead I want to show Some Other Message.

Is it possible?

ThanksI'm sorry, this is not possible.

-Daniel

Can we add tooltip to the Reports available in Report Manager?

Hi,
In my Report Manager window, I have created the Folder "Test" and under this folder I have uploaded 2 .rdl file. When I am placing the cursor over the Report Items, it shows 'Items in Test' instead I want to show Some Other Message.

Is it possible?

ThanksI'm sorry, this is not possible.

-Daniel

Can this be done without a Cursor

I know its better to do things without a cursor, but I think I may have a situation where I have no choice. I'm still pretty green with my SQL so I thought I'd put this question out there first. Here's the scenario:

With regards to stores ordering product from our warehouse.

Stores can reserve products that are on Purchase Orders that have not yet been received at the warehouse, so that when the product comes in it will be shipped out automatically. The file containing theses reservations is called "PoOrd". Anytime a regular order is processed for a store, the quantity shipped out is deducted from any reserved quantities in the "PoOrd" file. This way the store does not accidentally get overstocked.

PoOrd Example:

ProductID StoreNum PONum Quantity

93 1 121 5

93 2 121 8

100 1 121 10

100 1 221 10

100 1 321 10

101 4 221 7

Say I'm processing an order for Store # 1 that is shipping 15 units of Product # 100, and I need to take 15 units away from the PoOrd file starting from the PO with the largest number. So in other words POOrd should look like this after the Update.

ProductID StoreNum PONum Quantity

93 1 121 5

93 2 121 8

100 1 121 10

100 1 221 5

100 1 321 0

101 4 221 7

Am I right in thinking I have to use a Cursor. Anyone want to show me an example of how to do this.

If you are using SQL Server 2005 you should be able to accomplish this using a CTE and ROW_NUMBER function.

What follows is my first-pass attempt at this problem. I am not sure that I care for this much and I am also not sure if it is going to be any faster than a cursor-based method. It does appear to work, but in this case I am kind-of hoping that somebody knocks a hole in this approach:

declare @.stock table
( ProductId integer,
StoreNum integer,
PONum integer,
quantity integer
)

insert into @.stock
select 93, 1, 121, 5 union all
select 93, 2, 121, 8 union all
select 100, 1, 121, 10 union all
select 100, 1, 221, 10 union all
select 100, 1, 321, 10 union all
select 101, 4, 221, 7
--select * from @.stock

-- -- Table B4 Update: --

-- ProductId StoreNum PONum quantity
-- -- -- -- --
-- 93 1 121 5
-- 93 2 121 8
-- 100 1 121 10
-- 100 1 221 10
-- 100 1 321 10
-- 101 4 221 7

declare @.storeNum integer set @.storeNum = 1
declare @.productId integer set @.productId = 100
declare @.drawQty integer set @.drawQty = 15

;with stock as
( select row_number() over(order by PONum desc) as seq,
PONum,
storeNum,
ProductId,
quantity
from @.stock
where storeNum = @.storeNum
and productId = @.productId
), stockDraw as
( select seq,
PONum,
storeNum,
ProductId,
case when quantity >= @.drawQty
then quantity - @.drawQty
else 0
end as remainingQuantity,
case when quantity >= @.drawQty
then 0
else @.drawQty - quantity
end as remainingDraw
from stock
where seq = 1
union all
select b.seq,
b.PONum,
a.storeNum,
a.productId,
case when quantity > remainingDraw
then quantity - remainingDraw
else 0
end as remainingQuantity,
case when quantity > remainingDraw
then 0
else remainingDraw - quantity
end as remainingDraw
from stockDraw a
inner join stock b
on b.seq = a.seq + 1
and remainingDraw > 0
)
--select * from stockDraw
update @.stock
set quantity = remainingQuantity
from @.stock a
inner join stockDraw b
on a.storeNum = b.storeNum
and a.productId = b.productId
and a.PONum = b.poNum

select * from @.stock

-- ProductId StoreNum PONum quantity
-- -- -- -- --
-- 93 1 121 5
-- 93 2 121 8
-- 100 1 121 10
-- 100 1 221 5
-- 100 1 321 0
-- 101 4 221 7

|||

It looks like a better way of doing this is with an aggregate from a correlated subquery. This looks better:

declare @.storeNum integer set @.storeNum = 1
declare @.productId integer set @.productId = 100
declare @.drawQty integer set @.drawQty = 15

update @.stock
set quantity
= case when drawDown < 0 then 0
else a.quantity - drawdown
end
from @.stock a
inner join
( select PONum,
quantity,
( select sum(q.Quantity)
from @.stock q
where q.storeNum = @.storeNum
and q.productId = @.productId
and q.PONum >= a.PONum
) - @.drawQty as drawDown
from @.stock a
where storeNum = @.storeNum
and productId = @.productId
) b
on a.storeNum = @.storeNum
and a.productId = @.productId
and a.PONum = b.PONum
and a.quantity >= drawDown

select * from @.stock

-- ProductId StoreNum PONum quantity
-- -- -- -- --
-- 93 1 121 5
-- 93 2 121 8
-- 100 1 121 10
-- 100 1 221 5
-- 100 1 321 0
-- 101 4 221 7

|||Impressive! Thankyou. It will take a lot of practice before I'll be comming up with queries like that on my own.|||

In that case you have come to a good spot. The main reason that I worked this site into my daily routine was for the same reason -- I wanted to improve. Try to start by working through and replicating a few of the posts. I found that in the beginning I frequently made mistakes; I still do. But a lot of learning is in making the attempt.

In time you will understand where the answer is likely to be and you will be able to get there. I want to encourage you to give it a try and to also give yourself time to evolve. My email is available; if you ever have any questions give me a ring. I'm also here for you.

Kent

|||So say I put this Update query into a stored procedure and I need to perform it for each item on a shipment. How can I execute a process for each item without going row by row?|||

Chris:

I added a @.SHIPMENT table to the mockup; I really don't know if I have this set up correctly or not so I need your feedback. Please compare this query to the previous query so that you can see the additions and changes. It changed quite a bit:

declare @.stock table
( ProductId integer,
StoreNum integer,
PONum integer,
quantity integer
)

insert into @.stock
select 93, 1, 121, 5 union all
select 93, 2, 121, 8 union all
select 100, 1, 121, 10 union all
select 100, 1, 221, 10 union all
select 100, 1, 321, 10 union all
select 101, 4, 221, 7 union all
select 101, 1, 421, 20 union all
select 101, 1, 521, 30 union all
select 102, 1, 621, 20
--select * from @.stock

-- -- Table B4 Update: --

/*
ProductId StoreNum PONum quantity
-- -- -- --
93 1 121 5
93 2 121 8
100 1 121 10
100 1 221 10
100 1 321 10
101 4 221 7
101 1 421 20
101 1 421 30
102 1 521 20
*/

declare @.shipment table
( shipId integer,
storeNum integer,
productId integer,
shipQty integer
)
insert into @.shipment
select 101, 1, 93, 5 union all
select 101, 1, 100, 15 union all
select 101, 1, 101, 40 union all
select 102, 2, 93, 2
--select * from @.shipment
/*
shipId storeNum productId shipQty
-- -- -- --
101 1 93 5
101 1 100 15
101 1 101 40
102 2 93 2
*/

declare @.shipId integer set @.shipId = 101

;with shipList as
( select shipId,
storeNum,
productId,
shipQty,
shipQty as drawQty
from @.shipment
where shipId = @.shipId
), drawList as
( select shipId,
a.storeNum,
a.productId,
a.shipQty,
row_number() over
( partition by a.storeNum, a.productID
order by PONum desc
) as seq,
PONum,
b.quantity
from shipList a
inner join @.stock b
on a.storeNum = b.storeNum
and a.productId = b.productId
and b.quantity > 0
and a.shipId = @.shipId
), stockDraw as
( select shipId,
storeNum,
productId,
shipQty,
seq,
PONum,
case when quantity >= shipQty
then quantity - shipQty
else 0
end as remainingQty,
case when quantity >= shipQty
then 0
else shipQty - quantity
end as remainingDraw
from drawList
where seq = 1
union all
select a.shipId,
a.storeNum,
a.productId,
a.shipQty,
a.seq + 1 as seq,
b.PONum,
case when b.quantity >= remainingDraw
then b.quantity - remainingDraw
else 0
end as remainingQty,
case when b.quantity >= remainingDraw
then 0
else remainingDraw - b.quantity
end as remainingDraw
from stockDraw a
inner join drawList b
on b.seq = a.seq + 1
and a.productId = b.productId
and a.storeNum = b.storeNum
and remainingDraw > 0
)
update @.stock
set quantity = remainingQty
from stockDraw a
inner join @.stock b
on a.productId = b.productId
and a.storeNum = b.storeNum
and a.PONum = b.PONum

select * from @.stock

-- After Update -
/*
ProductId StoreNum PONum quantity
-- -- -- --
93 1 121 0
93 2 121 8
100 1 121 10
100 1 221 5
100 1 321 0
101 4 221 7
101 1 421 10
101 1 521 0
102 1 621 20
*/

Can the usage of a Cursor dynamically generate a query statement?

Hi,
As far as my understanding goes, cursors can be used to fill in variables as
it iterates through a pre-defined hardcoded query.
Would like to know if I can make a dynamic query within a cursor and
subsequently
pump values obtained from this dynamic query into their respective variables
.
Appreciate any form of reply! Thanks!
An extract of the code (it doesn't run):
DECLARE Scursor CURSOR FOR
SELECT isnull(@.account_colname, '')
, isnull(@.trans_date_colname, '')
, isnull(@.trans_time_colname, '')
, isnull(@.trans_type_colname, '')
, isnull(@.auxtrc_colname, '')
, isnull(@.auxtrc_colname, '')
, isnull(@.ref_no_colname, '')
, isnull(@.ref_no_colname, '')
, isnull(@.amount_colname, '')
, isnull(@.teller_colname, '')
, isnull(@.teller_colname, '')
, isnull(@.seq_colname, '')
-- ,ROWIDTOCHAR(rowid) --ROWID
FROM isnull(@.source_tabname, '')
WHERE isnull(CASE @.mode_day_all WHEN 'DAY' THEN @.p_trans_date END, '') +
isnull(@.erritx_colname, '') = isnull(@.erritx_flag, '')
AND isnull(@.contra_colname, '') IS NULL
OPEN ScursorHi Arthur,
The dynamic queries will be executed in a seperate memory space so u will
not be able to access any of the local variables. This holds good for your
cursors as well. u ll not be able to call them within your D-SQL.
rgds,
anu
"Arthur" wrote:

> Hi,
> As far as my understanding goes, cursors can be used to fill in variables
as
> it iterates through a pre-defined hardcoded query.
> Would like to know if I can make a dynamic query within a cursor and
> subsequently
> pump values obtained from this dynamic query into their respective variabl
es.
> Appreciate any form of reply! Thanks!
> An extract of the code (it doesn't run):
> DECLARE Scursor CURSOR FOR
> SELECT isnull(@.account_colname, '')
> , isnull(@.trans_date_colname, '')
> , isnull(@.trans_time_colname, '')
> , isnull(@.trans_type_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.amount_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.seq_colname, '')
> -- ,ROWIDTOCHAR(rowid) --ROWID
> FROM isnull(@.source_tabname, '')
> WHERE isnull(CASE @.mode_day_all WHEN 'DAY' THEN @.p_trans_date END, '') +
> isnull(@.erritx_colname, '') = isnull(@.erritx_flag, '')
> AND isnull(@.contra_colname, '') IS NULL
> OPEN Scursor
>
>|||Why cursors? Why dynamic SQL? Both are things that you should try to
avoid.
If you need more help, please specify your actual problem:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||The answer is yes, and the question is why do you want to do this?. Try
finding a set based solution to the problem and leave cursors as the last
resource in your pocket.
Example:
use northwind
go
declare @.my_cursor cursor
declare @.sql nvarchar(4000)
declare @.sd datetime
declare @.ed datetime
declare @.order_id int
declare @.order_date datetime
set @.sd = '19960101'
set @.ed = '19970101'
set @.sql = N'set @.c = cursor local fast_forward for select orderid,
orderdate from dbo.orders where orderdate >= '''
set @.sql = @.sql + convert(char(8), @.sd, 112) + N''' and orderdate < ''' +
convert(char(8), @.ed, 112) + N''''
set @.sql = @.sql + N'; open @.c'
execute sp_executesql @.sql, N'@.c cursor output', @.c = @.my_cursor output
if cursor_status('variable', '@.my_cursor') = 1
begin
while 1 = 1
begin
fetch next from @.my_cursor into @.order_id, @.order_date
if @.@.error <> 0 or @.@.fetch_status <> 0 break
select @.order_id, @.order_date
end
end
close @.my_cursor
deallocate @.my_cursor
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Arthur" wrote:

> Hi,
> As far as my understanding goes, cursors can be used to fill in variables
as
> it iterates through a pre-defined hardcoded query.
> Would like to know if I can make a dynamic query within a cursor and
> subsequently
> pump values obtained from this dynamic query into their respective variabl
es.
> Appreciate any form of reply! Thanks!
> An extract of the code (it doesn't run):
> DECLARE Scursor CURSOR FOR
> SELECT isnull(@.account_colname, '')
> , isnull(@.trans_date_colname, '')
> , isnull(@.trans_time_colname, '')
> , isnull(@.trans_type_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.amount_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.seq_colname, '')
> -- ,ROWIDTOCHAR(rowid) --ROWID
> FROM isnull(@.source_tabname, '')
> WHERE isnull(CASE @.mode_day_all WHEN 'DAY' THEN @.p_trans_date END, '') +
> isnull(@.erritx_colname, '') = isnull(@.erritx_flag, '')
> AND isnull(@.contra_colname, '') IS NULL
> OPEN Scursor
>
>