Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Sequential Numbering problem

    I've created a sproc that will provide a recordset for an Access report via a pass-thru query. The report is a production schedule.
    Some of the runs on the schedule have a note associated with them. I need
    to be able to number these notes, so that they can be displayed in the report
    bibliography style. When I populate the data in the sproc, if the "notes"
    field in my table contains any data I display a 1, else it's a 0
    Like:
    Code:
    SELECT NOTE_FLAG = CASE WHEN ISNULL(SCHED_NOTE,'')<>'',1,0
    FROM MASTER_SCHEDULE
    The problem is, I really need to display sequential numbers instead of 1's

    If I create a base set with this:
    Code:
    CREATE TABLE #TMPRST (
    	RECID INTEGER,
    	PRODUCT VARCHAR(10),
    	QTY FLOAT,
    	NOTE_FLAG INTEGER)
    
    INSERT INTO #TMPRST
    SELECT 1, 'ABC123', 4, 0
    UNION ALL
    SELECT 4, 'DEF123', 5, 1
    UNION ALL
    SELECT 5, 'ABC456', 12, 0
    UNION ALL
    SELECT 13, 'PQR789', 10, 1
    How do I go back and convert the note_flag column to read
    ... 0
    ... 1
    ... 0
    ... 2
    Inspiration Through Fermentation

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    1. do a select into with a temp table and the identity(int,1,1) function
    2. select from your temp temp table for your recordset.
    3. drop your temp table.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2006
    Posts
    87
    If speed is not an issue, you can use a cursor to loop through and update the lines in order. It means using a cursor, but it solves the problem.

    Code:
    DECLARE @MyValue    INTEGER
    DECLARE @MyRecID    INTEGER
    DECLARE @MyProduct  VARCHAR(10)
    DECLARE @MyQty      FLOAT
    DECLARE @MyNoteFlag INTEGER
    
    SET     @MyValue    = 0
    SET     @MyRecID    = 0
    SET     @MyProduct  = ''
    SET     @MyQty      = 0.0
    SET     @MyNoteFlag = 0
    
    CREATE TABLE #TMPRST (
    	RECID INTEGER,
    	PRODUCT VARCHAR(10),
    	QTY FLOAT,
    	NOTE_FLAG INTEGER)
    
    SET @MyValue = @MyValue + 0
    
    INSERT INTO #TMPRST
    SELECT 1, 'ABC123', 4, 0
    UNION ALL
    SELECT 4, 'DEF123', 5, 1
    UNION ALL
    SELECT 2, 'GHI123', 5, 1
    UNION ALL
    SELECT 3, 'JKL123', 5, 1
    UNION ALL
    SELECT 5, 'ABC456', 12, 0
    UNION ALL
    SELECT 6, 'DEF456', 12, 0
    UNION ALL
    SELECT 7, 'GHI456', 12, 1
    UNION ALL
    SELECT 8, 'JKL456', 12, 0
    UNION ALL
    SELECT 13, 'PQR789', 10, 1
    
    SELECT * FROM #TMPRST
    
    DECLARE MyCursor CURSOR FORWARD_ONLY
    FOR SELECT * FROM #TMPRST
    FOR UPDATE OF NOTE_FLAG
    
    OPEN MyCursor
    
    FETCH NEXT FROM MyCursor
    INTO @MyRecID, @MyProduct, @MyQty, @MyNoteFlag
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @MyValue = @MyValue + @MyNoteFlag
    
        UPDATE #TMPRST
        SET    NOTE_FLAG = @MyValue
        WHERE  RECID     = @MyRecID
        AND    PRODUCT   = @MyProduct
        AND    QTY       = @MyQty 
        AND    NOTE_FLAG = 1
    
        FETCH NEXT FROM MyCursor
        INTO @MyRecID, @MyProduct, @MyQty, @MyNoteFlag
    END
    
    SELECT * FROM #TMPRST
    
    CLOSE      MyCursor
    DEALLOCATE MyCursor
    DROP TABLE #TMPRST
    I hope this helps...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Code Carpenter
    you can use a cursor to loop through and update the lines in order. It means using a cursor, but it solves the problem.
    Lol - you did like that article.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by Thrasymachus
    1. do a select into with a temp table and the identity(int,1,1) function
    2. select from your temp temp table for your recordset.
    3. drop your temp table.
    I can work with that.
    Thanks
    Inspiration Through Fermentation

  6. #6
    Join Date
    Jul 2006
    Posts
    87
    Quote Originally Posted by pootle flump
    Lol - you did like that article.
    I DID, I DID, I DID like the article! (wiki tweety)

    And notice that I added data, too. I ain't gonna get tricked by a tie twice!

    And, like it said, he could do this on the front end SO much easier.

    I guess front end folks can't handle incrementing variables.

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What article are you referring to?

    My first instinct was to use a cursor, but I've read enough here to know
    that they are "Evil", so I try to stay away from them now.
    Inspiration Through Fermentation

  8. #8
    Join Date
    Jul 2006
    Posts
    87
    Quote Originally Posted by RedNeckGeek
    What article are you referring to?

    My first instinct was to use a cursor, but I've read enough here to know
    that they are "Evil", so I try to stay away from them now.
    Hi RNG,

    From this thread,
    http://www.dbforums.com/showthread.p...88#post6221588
    see post #18 by Pootle Flump. Cursors ARE evil for general use, but when you need a running tally and just can't get it done on the front end, your options are limited.

    BTW, does a red neck geek have an OS/2 Flag on the back of their pickup truck? (OS/2 Shall Rise Again!)

    just curious.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm wondering under what circumstances "you need a running tally and just can't get it done on the front end"

    at that point i would be inclined to question either the front end, or the guy developing in it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    i'm wondering under what circumstances "you need a running tally and just can't get it done on the front end"

    at that point i would be inclined to question either the front end, or the guy developing in it

    Have you ever had to manage a staff of developers?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    Have you ever had to manage a staff of developers?
    yes, i have

    your point is... ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2006
    Posts
    87
    Quote Originally Posted by r937
    i'm wondering under what circumstances "you need a running tally and just can't get it done on the front end"

    at that point i would be inclined to question either the front end, or the guy developing in it
    In this case, he was passing it on to Access for some other report. Maybe they only have the MDE and cannot change the report?

  13. #13
    Join Date
    Jan 2006
    Location
    Los Angeles, CA
    Posts
    63
    Quote Originally Posted by r937
    yes, i have

    your point is... ?
    Rudy .. you know Brett .. most of his posts are pointless anyways Just kidding!

    I think what he meant is that developpers are more likely to screw things up compared to .. let's say .. DBA(s)?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, trying to stay on topic here, if i were still managing developers, and one of them had trouble incrementing a counter, they wouldn't be working for me for very long...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    BTW, does a red neck geek have an OS/2 Flag on the back of their pickup truck? (OS/2 Shall Rise Again!)
    heh I do have Nascar pocket protector and a Skoal sticker on my laptop.
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •