Блог

Меня попросили включить поле ComNo, и если длина поля комментариев превышает 5 символов, остальные символы в комментарии должны появиться в следующей строке, а поле ComNo должно быть увеличено на единицу

Ввод

 EmpID EmpName ServiceNumber Date         Comments
1     a      123            23-03-1990   wednesday
1     a      1234           24-04-1990   Test12
2     b      234            24-05-2016   Todayis
  

И ожидаемый результат

 EmpID EmpName ServiceNumber Date       ComNo Comments
1     a       123           23-03-1990 1     wedne
1     a       123           23-03-1990 2     sday
1     a       1234          24-04-1990 1     Test1
1     a       1234          24-04-1990 2     2
2     b       234           24-05-2016 1     Today
2     b       234           24-05-2016 2     is
  

У меня есть идея, как реализовать это с помощью процедуры plsql, но можем ли мы реализовать то же самое с помощью sql-запроса?

Вот решение, которое не делает предварительных предположений о длине комментариев. Я добавил две строки, одну короткую (менее 5 символов) и одну длиннее 10 символов, а также одну с комментарием NULL, чтобы убедиться, что строка не потеряна, чтобы тщательно протестировать решение.

Я предполагаю (empid, dt) , что это уникальная комбинация (возможно, первичный ключ) в базовой таблице. Кстати, я надеюсь, что вы на самом деле не используете Date в качестве имени столбца в своей схеме.

Решение не содержит предложения WITH; оно начинается с select empid... предложения ORDER BY, которое также может не понадобиться.

 with
     test_data ( empid, empname, servicenumber, dt, comments ) as (
       select 1, 'a',  123, to_date('23-03-1990', 'dd-mm-yyyy'), 'wednesday'   from dual union all
       select 1, 'a', 1234, to_date('24-04-1990', 'dd-mm-yyyy'), 'Test12'      from dual union all
       select 2, 'b',  234, to_date('24-05-2016', 'dd-mm-yyyy'), 'Todayis'     from dual union all
       select 2, 'b',  235, to_date('25-05-2016', 'dd-mm-yyyy'), 'Joe'         from dual union all
       select 3, 'c',  238, to_date('25-05-2016', 'dd-mm-yyyy'), ''            from dual union all
       select 4, 'c', 2238, to_date('25-05-2016', 'dd-mm-yyyy'), 'longer string' from dual
     )
select empid, empname, servicenumber, dt, level as comno,
       substr(comments, 5 * level - 4, 5) as comments
from   test_data
connect by level <= 1   length(comments) / 5
     and   prior empid = empid
     and   prior dt    = dt
     and   prior sys_guid() is not null
order by empid, dt
;

     EMPID E SERVICENUMBER DT              COMNO COMMENTS
---------- - ------------- ---------- ---------- --------------------
         1 a           123 1990-03-23          1 wedne
         1 a           123 1990-03-23          2 sday
         1 a          1234 1990-04-24          1 Test1
         1 a          1234 1990-04-24          2 2
         2 b           234 2016-05-24          1 Today
         2 b           234 2016-05-24          2 is
         2 b           235 2016-05-25          1 Joe
         3 c           238 2016-05-25          1
         4 c          2238 2016-05-25          1 longe
         4 c          2238 2016-05-25          2 r str
         4 c          2238 2016-05-25          3 ing
  

Добавлено: если ваши данные имеют тип данных CLOB, лучше использовать dbms_lob версию substr . Кроме того, если вы должны разделить данные на 75 символьных сегментов, вы должны настроить несколько чисел. Вот то же самое решение, с этими двумя изменениями и без предложения ORDER BY (не требуется, если это используется для переноса данных в другой продукт БД). ПРИМЕЧАНИЕ: dbms_lob.substr() имеет ли значение «сумма» и «смещение» (второй и третий аргументы) обратные по сравнению с обычной substr() функцией; обратите внимание, что при сравнении решений.

 select empid, empname, servicenumber, dt, level as comno,
       dbms_lob.substr(comments, 75, 75 * level - 74) as comments
from   test_data
connect by level <= 1   length(comments) / 75
     and   prior empid = empid
     and   prior dt    = dt
     and   prior sys_guid() is not null
;
  

Вот один из способов сделать это:

 with n as (
      select 1 as n from dual union all
      select 2 from dual
     )
select EmpID, EmpName, ServiceNumber, Date, ComNo,
       substr(comments, n.n * 5 - 4, 5) as Comments
from t join
     n
     on length(comments) >= n.n * 5   1;
  

Примечание: в вашем примере есть только комментарии длиной до 10 символов, поэтому нужны только значения 1 и 2 n . Вы можете создавать дополнительные строки, расширяя n .

Вариантом грубой силы было бы просто UNION объединить записи, полученные в результате разделения комментариев на 5 символов за раз:

 SELECT EmpID, EmpName, ServiceNumber, Date, 1, SUBSTR(Comments, 1, 5) AS Comments
FROM yourTable
UNION ALL
SELECT EmpID, EmpName, ServiceNumber, Date, 2, SUBSTR(Comments, 6, 5) AS Comments
FROM yourTable
WHERE SUBSTR(Comments, 6, 5) <> ''  -- but don't show a record if
UNION ALL                           -- all characters already used
...