How to use the cursor to traverse the table when a certain condition is met to write to another table all the records above the cursor, in which the value in one field is repeated in order?

Scheme

Suppose there is a similar label. In it, the fields are sorted by the field NOM_V . As means of the C language and embedded SQL if the conditions coincide:

1) Some condition was fulfilled in the adjacent field (for example, it contains NULL )

2) This is the last entry with a C value in the NOM_V field

arrange INSERT to another table of all records containing the value of С in NOM_VAG . (in this case, the record [4,5,6] ).

upd. The case may not be unique. It is required to select records from the entire table where these conditions are the same. Those. if the condition in field1 fulfilled in record 9, then, in addition to [4,5,6] , [7,8,9] must also be hooked. And so on to the very end.

  • one
    And why is there a cursor at all? It depends, of course, on the SQL dialect used, but generally speaking, such things are done in one query. - Yaant
  • @Yaant The fact is that this is far from the only condition. It may be that with some SQL tools it would be possible to select everything at once, but I am still a neophyte in this matter, so it’s unlikely that I will be able to push everything into one query. - LittleTofu
  • And you can do this with a cursor only if you memorize all the records you have passed somewhere in the array in memory or by inserting it into a temporary table, and then transferring everything from the temporary table according to the condition. So one request would be preferable. And you really DB2 SQL? some kind of exotic ... - Mike
  • Although I was too smart. temporary tables are not needed. read your request and insert records as soon as the condition worked just exit the reading cycle. it remains to decide what to do if you have read it to the end but the condition did not work ... - Mike
  • @Mike Isn’t it an option to use a SCROLL cursor? Probably, if it doesn't work out, I will solve the problem using an array. - LittleTofu

1 answer 1

the cursor is needed because There is no explicit field that indicates that this particular string is the last with this NOM_V. Those. information is also needed on the previous adjacent line (the fact that the value of the NOM_V field has changed). Inside the cursor block, remember the value of NOM_V and the fact that the 'condition' is met. And before that - check this data saved on the previous iteration. And if NOM_V has changed and the 'condition' has been met - select all rows with the previous value NOM_V and pour into a new table. And only then save the new value NOM_V in the variables and the fact that the 'condition' is met. After passing the cursor to the end, if the condition on the last line (saved at the last iteration of the cursor) is met, also fill the new table with the last NOM_V saved to the new table. The specific syntax of the procedure, I think, is not critical

  • People, sorry, did not notice that the question is also labeled db2. not sure if there is a 'cursor' - it means the same thing as in more modern types of databases - ale