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?
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
;