Solved

Value at Position-Oriented  SQL Server 2005  While Loop

Posted on 2006-07-24
7
230 Views
Last Modified: 2012-05-05

Hi there!

When I code:

SELECT SgnQtyShares FROM dbo.EqtyTransactions
    Where (StockID = 48) AND (SgnQtyShares < 0)
ORDER BY EqtyTransID

I get (correctly) the following:

    SgnQtyShares
1    -1000
2    -1000
3    -750
4   -1000

And I want to put this SQL inside a "While" loop with the following additional restriction: at each loop, I have to get the "next" SgnQtyShares field value, from up to bottom.

In other words, I want to implement something like:

-------------------
CREATE FUNCTION dbo.StockFifoNegMatrix(@StockID int)
   RETURNS   * SOMETHING *
AS
BEGIN

DECLARE @RowCounter int
SET @RowCounter  = 0

WHILE @RowCounter < = ISNULL(dbo.NbPositSharesPerStock(StockID), 0) - 1
  BEGIN
    aux_d =

 SELECT SgnQtyShares FROM dbo.EqtyTransactions  Where (StockID = 48) AND (SgnQtyShares < 0)
 AND  *** HERE IS WHERE I HAVE TO PUT SOMETHING  ****
ORDER BY EqtyTransID

  * ETC *

  END
RETURN

END
-------------------------

Where the "something"  is related to the @RowCounter , of course.

I mean, first time at loop, please get the -1000 value, second time, again -1000, third time, -750 etc...

Please show me what should I put in the blank space.

Thanks,
fskilnik.




0
Comment
Question by:fskilnik
  • 4
  • 3
7 Comments
 
LVL 11

Expert Comment

by:deroby
ID: 17167912
I think that you're mixing up things

For starters, as far as I know, a function cannot return multiple recordsets, even when you define them as returning n*SOMETHING*  =)

However, you could do this in a stored procedure.

From what you tell, I understand that you want to return multiple recordsets, each one returning 1 row (value) at a time ??

(if I may ask, how do you plan to use this in the 'next step' ?)
0
 

Author Comment

by:fskilnik
ID: 17168074
Hi, deroby!

Thanks for the quick reply.

>> For starters, as far as I know, a function cannot return multiple recordsets

Ok. I am sorry not to explicit that. This is an user defined function that returns a table... and the "extract" I showed is only a small part of it.

>> However, you could do this in a stored procedure.

I know. But I guess my approach would be better for what I have in mind, in the sense that the stored procedure would need a "EXEC" to trigger it and I want to avoid this if possible. (through Views "triggering" some functions, those functions  call this function you are helping me with...)

>> From what you tell, I understand that you want to return multiple recordsets, each one returning 1 row (value) at a time ??

No. It´s harder than that... and there will be much more lines in the * ETC *   :)

If you want to know exactly what I have in mind, please have a look at this algorithm:

 For i = 0 To NumberSharesUsed - 1
        aux_d = aux_a(i)                                 << -------------------------------------------------------
        For j = 0 To NumberSolicNeg - 1
            If NegStockID(j) <> StockID(i) Then Exit For
            If aux_d >= Abs(aux_b(j)) Then
                aux_c(i, j) = Abs(aux_b(j))
                aux_d = aux_d - aux_c(i, j)
                aux_b(j) = 0
            Else
                aux_c(i, j) = aux_d
                aux_b(j) = aux_b(j) + aux_d
                aux_d = 0
            End If
    Next j, i

You are helping me in the mentioned row above!   (Now variables are different. I put here just for your curiosity...)

0
 
LVL 11

Accepted Solution

by:
deroby earned 300 total points
ID: 17168511
somehow I was assuming this would be conversion from language to another.

Please keep in mind that SQL does NOT like to be handled on 'row-by-row' basis, but rather prefers 'bulk' operations.

Anyway, if you want to stick to the orignal logic you probably want to use a cursor (which is not adviced because of reason above, but as an exception I'll give you the syntax anyway =)

I guess this should get you started for the outer loop, to break out of the loop I've added a bit-variable that you can set to 0 somewhere inside the loop (here WHERE 1 = 2, so in reality it will never get there off course... or at least, so I hope =)

DECLARE @c_SgnQtyShares int
DECLARE @keep_looping_ind bit

SELECT @keep_looping_ind = 1

OPEN outer_loop CURSOR LOCAL
 FOR SELECT SgnQtyShares
       FROM dbo.EqtyTransactions
      WHERE (StockID = 48)
        AND (SgnQtyShares < 0)
      ORDER BY EqtyTransID
OPEN outer_loop
FETCH NEXT FROM outer_loop INTO @c_SgnQtyShares
WHILE @@FETCH_STATUS = 0 AND @keep_looping = 1
    BEGIN

        PRINT @c_SgnQtyShares

        IF 1 = 2
            SELECT @keep_looping_ind = 0
   
        FETCH NEXT FROM outer_loop INTO @c_SgnQtyShares
    END
CLOSE outer_loop
DEALLOCATE outer_loop
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:fskilnik
ID: 17168656

Great, deroby. That´s the kind of "structure" I had in mind.

>> Please keep in mind that SQL does NOT like to be handled on 'row-by-row' basis, but rather prefers 'bulk' operations.

I see. But this is a FIFO ("first in, first out") algorithm, therefore "sequential" by its own nature...

Please consider already the points given to you but wait a bit longer (say 2 more days, at most) for them because I may have a specific doubt in implementing your idea. Then I will ask and, of course, if I give you more "trouble" I will increase the points before giving them all to you, ok?!

Thanks a lot,
fskilnik.

0
 
LVL 11

Expert Comment

by:deroby
ID: 17168686
No problem, I tend to be on EE whenever I have some "long-running" tests going on, so let's hope that the boss has more of them scheduled the coming days =)
0
 

Author Comment

by:fskilnik
ID: 17169851
I´m back, deroby.

Thanks a lot. It worked as expected (thanks) !   I will increase to 300 points because the "cursor possibilities" are exactly what I needed!

Best regards,
Fábio.


P.S.: just for the record:  where you wrote  "OPEN outer_loop CURSOR LOCAL" you certainly meant
"DECLARE outer_loop CURSOR LOCAL"  ;)  !
0
 
LVL 11

Expert Comment

by:deroby
ID: 17174178
Yep, seems I mistyped that part ... mea culpa
(I'm not not used to using cursors, avoiding them wherever possible =)

happy coding...
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now