SQL SERVER | Fill empty fields in select

Asked

Viewed 720 times

2

I have a select in a table:

select a.linha, a.NUM_LCTO, a.DT_LCTO from arquivo a order by 1

That returns me the values as follows (I put only a few lines, the total result is about 800000 lines.):

linha       NUM_LCTO                                                                                                                                                                                                                                                         DT_LCTO
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9544        7627367-0101                                                                                                                                                                                                                                                     01012018
9545                                                                                                                                                                                                                                                                          
9546                                                                                                                                                                                                                                                                          
9547        7627371-0101                                                                                                                                                                                                                                                     01012018
9548                                                                                                                                                                                                                                                                          
9549                                                                                                                                                                                                                                                                          
9550        7627373-0101                                                                                                                                                                                                                                                     01012018
9551                                                                                                                                                                                                                                                                          
9552                                                                                                                                                                                                                                                                          
9553        7627374-0101                                                                                                                                                                                                                                                     01012018
9554                                                                                                                                                                                                                                                                          
9555                                                                                                                                                                                                                                                                          
9556                                                                                                                                                                                                                                                                          
9557                                                                                                                                                                                                                                                                          
9558        7627375-0101                                                                                                                                                                                                                                                     01012018
9559                                                                                                                                                                                                                                                                          
9560                                                                                                                                                                                                                                                                          
9561        7627376-0101                                                                                                                                                                                                                                                     01012018
9562                                                                                                                                                                                                                                                                          
9563                                                                                                                                                                                                                                                                          
9564

I need to fill the empty columns of "NUM_LCTO" and "DT_LCTO", with the value of the filled rows, respecting the respective value changes. The result should be similar to this:

linha       NUM_LCTO                                                                                                                                                                                                                                                         DT_LCTO
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9544        7627367-0101                                                                                                                                                                                                                                                     01012018
9545        7627367-0101                                                                                                                                                                                                                                                     01012018
9546        7627367-0101                                                                                                                                                                                                                                                     01012018
9547        7627371-0101                                                                                                                                                                                                                                                     01012018
9548        7627371-0101                                                                                                                                                                                                                                                     01012018
9549        7627371-0101                                                                                                                                                                                                                                                     01012018
9550        7627373-0101                                                                                                                                                                                                                                                     01012018
9551        7627373-0101                                                                                                                                                                                                                                                     01012018
9552        7627373-0101                                                                                                                                                                                                                                                     01012018
9553        7627374-0101                                                                                                                                                                                                                                                     01012018
9554        7627374-0101                                                                                                                                                                                                                                                     01012018
9555        7627374-0101                                                                                                                                                                                                                                                     01012018
9556        7627374-0101                                                                                                                                                                                                                                                     01012018
9557        7627374-0101                                                                                                                                                                                                                                                     01012018
9558        7627375-0101                                                                                                                                                                                                                                                     01012018
9559        7627375-0101                                                                                                                                                                                                                                                     01012018
9560        7627375-0101                                                                                                                                                                                                                                                     01012018
9561        7627376-0101                                                                                                                                                                                                                                                     01012018
9562        7627376-0101                                                                                                                                                                                                                                                     01012018
9563        7627376-0101                                                                                                                                                                                                                                                     01012018
9564        7627376-0101                                                                                                                                                                                                                                                     01012018

Can someone help us? I tried to build the field with subquery, but at some point it gets lost and does not mess up the codes.

database sql-server

2 answers

3


Use a OUTER APPLY in the same table to select the last completed record before you want:

SELECT a.linha,
       CASE ISNULL(a.num_lcto, '') WHEN '' THEN ref.num_lcto ELSE a.num_lcto END AS num_lcto
  FROM arquivo a
  OUTER APPLY (
    SELECT TOP(1) a2.linha,
                  a2.num_lcto
      FROM arquivo a2
     WHERE a2.linha < a.linha
       AND ISNULL(a2.num_lcto, '') <> ''
     ORDER BY a2.linha DESC
  ) ref

Resulting in:

linha   | num_lcto
----------------------
9544    | 7627367-0101
9545    | 7627367-0101
9546    | 7627367-0101
9547    | 7627371-0101
9548    | 7627371-0101
9549    | 7627371-0101
9550    | 7627373-0101
9551    | 7627373-0101
9552    | 7627373-0101
9553    | 7627374-0101
9554    | 7627374-0101
9555    | 7627374-0101
9556    | 7627374-0101
9557    | 7627374-0101
9558    | 7627375-0101
9559    | 7627375-0101
9560    | 7627375-0101
9561    | 7627376-0101
9562    | 7627376-0101
9563    | 7627376-0101
9564    | 7627376-0101

See working on SQL Fiddle.

  • 2

    Thanks for the help, for the 800000 lines I needed to create a "clustered" index to improve the performance a little, I don’t know if I can improve more, but it already helped. Thanks!

0

As it is a select you cannot fill in the empty fields. Just pq is a select. You will have to select all rows with empty table fields, change to the value you want and update and then select again.

Browser other questions tagged database sql-server

You are not signed in. Login or sign up in order to post.