Annie Jeba Annie Jeba - 2 months ago 6
SQL Question

Auto increment counter if field length is greater than n

I have been asked to include a field ComNo, and if field length of Comments is greater than 5 characters, the rest of characters in Comment should appear in next line and ComNo field should be incremented by one

Input

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


And the expected output is

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


I have an idea how to implement this using plsql procedure but can we implement the same using sql query?

Answer

Here is a solution that makes no prior assumptions about the length of comments. I added two strings, one short (less than 5 characters) and one longer than 10 characters, as well as one with NULL comment to make sure the row is not lost, to test the solution thoroughly.

I assume (empid, dt) is a unique combination (perhaps primary key) in the base table. By the way, I hope you are not actually using Date as a column name in your schema.

The solution does not contain the WITH clause; it begins at select empid... The ORDER BY clause may not be needed either.

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

Added: If your data is of CLOB data type, it is better to use the dbms_lob version of substr. Also, if you must chop the data into 75 character segments, you must adjust several numbers. Here is the same solution, with these two changes and without the ORDER BY clause (not needed if this is used to migrate the data to another db product). NOTE: dbms_lob.substr() has the "amount" and the "offset" (second and third arguments) reversed compared to the usual substr() function; notice that when you compare the solutions.

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
;