Is there a simple SQL statement for indexing records with a set range?

Discussion in 'OT Technology' started by 5Gen_Prelude, Jun 2, 2003.

  1. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    This sounds simple enough but the only way I've been able to do this is cycle through each record one by one in vb and assign it a cheque number (for preprinted cheques) and keeping track of the last cheque number ie:

    Code:
    while not .eof
         !ChequeNumber = x
         x = x + 1
         .movenext
    wend
    
    This doesn't seem very elegant.
     
  2. CompiledMonkey

    CompiledMonkey New Member

    Joined:
    Oct 26, 2001
    Messages:
    8,528
    Likes Received:
    0
    Location:
    Richmond, VA
    I'm not familiar with the terminology but by index, are you saying sort? And this is on the client side, right?
     
  3. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    No, it's not just for sorting. And yeah - think of a table with the address, amount to be paid, the date... etc. Now, you also have to keep track of the Cheque Number which is a variable (you've got a stack of preprinted cheques with all the banking information and the cheques are already numbered). You have to make sure the information on record 1 gets printed on cheque number 12334, 2 on 12335 etc... And you have to know which cheque went with which amount which is why you have update the cheque number field in the original table.

    In other words - it's no problem to assign cheque 12334 to every single record but that's not helpful
     
  4. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Um, you're explanation isn't all that clear...

    It sounds like you're trying to sort the index as well as associate one index with another index.

    Also, just so we know what SQL tricks you have available, what DBMS are you using?

    I have some ideas, but I'm not sure I'm on the same page as you...
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    :uh: I can't believe no one understands this - it's such a simple problem - maybe my head isn't on straight. Imagine a table:

    Code:
    Total     Name      ChequeDate     ChequeNumber
    $12.30    Jim Bob   5/12/03
    $34.29    Zippy     5/13/03
    
    Now they are going to printed on cheques 15 and 16 on a report. That's fine. Now, how do you assign the number 15 to the first cheque, 16 to the second etc... I provided a means using VB, I'm wondering how to do it via one SQL statement. If I wanted to update all the records with the same value, there's no problem obviously - but in this case the value needs to increase as the update process goes along.

    One way to do it is to add an autonumber field (ChequeID) and use it to offset the ChequeNumber. I would first do a query to find out the lowest value (MinOfChequeNumber), then assign the ChequeNumber: ChequeID-MinOfChequeID+ChequeNumberStart where ChequeNumberStart is 15.
     
  6. CompiledMonkey

    CompiledMonkey New Member

    Joined:
    Oct 26, 2001
    Messages:
    8,528
    Likes Received:
    0
    Location:
    Richmond, VA
    Your explanation wasn't so clear. I understand the question now but I'm not sure how you can do it in SQL.
     
  7. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Ahhh! I see what you're saying now...

    Code:
    
    var nCheckOffset = 2000
    var sQuery = "SELECT Total, Name, ChequeDate, (ChequeID + " + nCheckOffset + ") AS ChequeNumber FROM myTable ORDER BY ChequeNumber"
    
    If that makes sense, that might be one way to do it. It doesn't really give me that warm and fuzzy feeling inside (what if an ID is deleted?).

    But what ends up happening is you take your ID and add the offset to it to get your final check number. SQL can definitely do this. Watch which flavor DBMS you're using. The above will work in MySQL, but MS SQL (v6/7/2k) may complain about the order by field being a calculated field. If so, you may need to create a temp table (or better yet, create a stored procedure that creates the view and query the stored procedure).

    This come close to what you're looking for?
     
  8. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    That's pretty much what I had - the problem with your example is the ChequeID doesn't necessarily start at 0 or 1 - it could start at 1457. So you first have to figure out what it starts with (MinofChequeID) in a separate query, then subtract it from the chequeid and add the ChequeStart Number. That's assuming ChequeID is sequential - if not it'll mess it up. Oh well, the VB way works fine, it's just didn't seem tidy to me.
     

Share This Page