Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: Using variables for specific rows -?

    Hello,

    I hope you can help. I have an SQL table (imported from another data source) that has a group of rows that make up one record. However, the record ID is only in the "Segment" labeled MSH, in col010 of the row.

    What I would like to do is to get that value and assign it to the col010 field in subsequent rows until Segment = MSH again. Then get that new number and assign it to the col010 fields, etc.

    This is a piece of my table:

    Segment col010
    MSH 29958
    EVN NULL
    PID NULL
    PV1 NULL
    FT1 NULL
    PR1 NULL
    ZCF NULL
    MSH 29959
    EVN NULL
    PID NULL
    PV1 NULL
    FT1 NULL
    PR1 NULL
    ZCF NULL

    I've tried several combinations of if statements and while loops and update statements. But I'm afraid I don't know enough about MS SQL to make it work.

    Any suggestions appreciated!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, you see, there's a problem here

    it's impossible to pinpoint the "subsequent rows" simply because in a relational database table, there is no sequence to the rows

    you'll have to do this outside the database, before importing the data from the other source
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    The records are brought in to the SQL table in sequence.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by spolak
    The records are brought in to the SQL table in sequence.
    I wouldn't doubt that for an instant. The problem is that just because the rows came into SQL Server "in sequence" doesn't mean diddly about being able to get them back out "in sequence" unless you do something like adding a column with either sequence or time of insert information.

    If you have a lot of faith and don't mind the occasional sequencing disaster, you can rely on some tricks that work a lot more often than they don't work, but there is no guarantee that it will work on any given run and a virtual certainty that it will break now and then.

    This isn't good enough for most folks. Can you live with it?

    -PatP

  5. #5
    Join Date
    Mar 2009
    Posts
    3
    I would love to give it a try.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by spolak
    The records are brought in to the SQL table in sequence.
    what's wrong with using a simple programming language on the sequenced data before bringing it into the database?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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