Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    5

    Unanswered: MS SQL problem: fill down variable number of rows from last known value.

    Using Microsoft query analyser SQL version 8, I believe.

    Got a SQL table with a column that has NULLS in it. What I want to do is fill down the value in the row above based on a condition in another column (recordtype). Came up with the following code but unfortunately it only fills in the record directly below. Not those further down (variable number of NULL rows). Any idea how to get these to fill down? Suspect the answer may lie with block cursors but 1) I'm told they're slow (I have 2 and 1/2 million records in this table) and 2) I've never used cursors before!

    ________________

    Update dbo.NSTS

    Set dbo.NSTS.Number = NSTS_1.Number

    FROM dbo.NSTS LEFT OUTER JOIN
    dbo.NSTS AS NSTS_1 ON dbo.NSTS.[Id] = ((NSTS_1.[Id])+1)

    WHERE dbo.NSTS.RecordType like '101' AND (dbo.NSTS.Number is null or dbo.NSTS.Number like '')

    ________________

    Example of data table here:

    ID RecordType Number
    1 100 4325273182
    2 101 NULL
    3 101 NULL
    4 101 NULL
    5 100 4342684020
    6 100 4342696800
    7 100 4304028782
    8 100 4305914743
    9 101 NULL
    10 101 NULL
    11 100 4328179942
    12 101 NULL
    13 101 NULL
    14 101 NULL
    15 101 NULL
    16 100 4329313804
    17 100 4329874164
    18 101 NULL
    19 100 4330053764
    20 101 NULL
    _________________

    I did get a cursor loop working but can only retrieve 1 row and (try as I might with the so called help examples) can't seem to pull down 2 rows at a time. Just can't seem to get it to accept this command at all: SQLSetStmtAttr(hStmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) 2 ,0).

    Help would be very much appreciated!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    not sure I understand...when you say fill....fill with what?

    Also the order of data in a database is meaningless, so what does "next" mean?
    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.

  3. #3
    Join Date
    May 2006
    Posts
    5

    Red face Fill down

    Beg your pardon. Can't see the wood for the trees.

    In this case the data is in a strictly sequential order as defined by the ID field. So, first has ID = 1 and next = current ID + 1.

    Data comes from another source in a rather weird format which I won't bother you with. Each row with recordtype of 100 has latest data. If something has changed in the past then there will be another row with a recordtype of 101 and with the detail of the value that changed in the appropriate column. If a column hasn't changed then a NULL will be present. In this instance, I want to take the value in that column (Number in this instance) from the row that has an ID of ID-1 (the newer record.) It should be noted that it is possible to have NULL values in the number field so you could have a record type of 100 with a NULL value. In this case it needs to be left as NULL.

    Have I explained my dilema appropriately? I've actually just tried doing this with a while (count(recordtype) ... where Number is null >0) type statement before I realised there actually were valid nulls in the Number column!

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by --Grrrrrr--
    Beg your pardon. Can't see the wood for the trees.

    Guess I'm not smart enough to help on this.

    Good Luck
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is actually a good puzzle, but I don't have enough time to solve it before my next meeting. If nobody else solves it before then, I'll do it tonight.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry I didn't get this one last night. It was still a fine puzzle!
    Code:
    CREATE TABLE #NSTS (
       id		INT		NOT NULL
    ,  record	INT		NOT NULL
    ,  number	BIGINT		NULL
       )
    
    INSERT INTO #NSTS (id, record, number)
       SELECT  1, 100, 4325273182	UNION
       SELECT  2, 101, NULL		UNION
       SELECT  3, 101, NULL		UNION
       SELECT  4, 101, NULL		UNION
       SELECT  5, 100, 4342684020	UNION
       SELECT  6, 100, 4342696800	UNION
       SELECT  7, 100, 4304028782	UNION
       SELECT  8, 100, 4305914743	UNION
       SELECT  9, 101, NULL		UNION
       SELECT 10, 101, NULL		UNION
       SELECT 11, 100, 4328179942	UNION
       SELECT 12, 101, NULL		UNION
       SELECT 13, 101, NULL		UNION
       SELECT 14, 101, NULL		UNION
       SELECT 15, 101, NULL		UNION
       SELECT 16, 100, 4329313804	UNION
       SELECT 17, 100, 4329874164	UNION
       SELECT 18, 101, NULL		UNION
       SELECT 19, 100, 4330053764	UNION
       SELECT 20, 101, NULL
    
    SELECT a.id, a.record, (SELECT b.number
       FROM #NSTS AS b
       WHERE b.id = (SELECT Max(c.id)
          FROM #NSTS AS c
          WHERE  c.number IS NOT NULL
             AND c.id BETWEEN (SELECT Max(d.id)
                FROM #NSTS AS d
                WHERE  d.id <= a.id
                   AND d.record = (SELECT Min(e.record)
                   FROM #NSTS AS e)) AND a.id))
       FROM #NSTS AS a
    
    DROP TABLE #NSTS
    -PatP

  7. #7
    Join Date
    May 2006
    Posts
    5
    Wow. What's it like in your mind? All flashing cogs and some sort of weird matter distortion device that bends reality into the kind of shapes normally only encountered on class A drugs?

    Many thanks for this. Much more elegant than my attempt. Actually got that to work in the end by using:

    ________

    While (select count(RecordType) from NSTS where ((Number is null) and (RecordType like '101'))) > (select count(RecordType) from NSTS where ((Number is null) and (RecordType like '100')))

    Update NSTS

    Set NSTS.Number = Tbl_NSTS_PracticeDownloads_1.Number

    FROM NSTS LEFT OUTER JOIN
    NSTS AS Tbl_NSTS_PracticeDownloads_1 ON NSTS.[Id] = ((Tbl_NSTS_PracticeDownloads_1.[Id])+1)

    WHERE NSTS.RecordType like '101' AND (NSTS.Number is null or NSTS.Number like '')

    _________

    Question now is: which will crunch fastest with 2/1/2 million records? Guess I'll have to find out!

    Once again, many thanks for your help on this!

Posting Permissions

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