By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table2 as
with t as (
SELECT '010125 | BILL/17/001 | 0 | C | 6 |
+--------+-------------+-----------+------------+--------+
| 010125 | BILL/17/002 | 0 | I | 1 |
+--------+-------------+-----------+------------+--------+
| 010125 | BILL/17/003 | 0 | F | 1 |
+--------+-------------+-----------+------------+--------+
| 010125 | BILL/17/004 | 0 | C | 6 |
+--------+-------------+-----------+------------+--------+
| 010113 | BILL/17/005 | 0 | C | 6 |
+--------+-------------+-----------+------------+--------+
| 010113 | BILL/17/006 | 0 | I | 1 |
+--------+-------------+-----------+------------+--------+
| 010048 | BILL/17/007 | 0 | C | 6 |
+--------+-------------+-----------+------------+--------+
| 010071 | BILL/17/008 | 0 | C | 6 ' as str from dual)
SELECT to_number(regexp_substr( t.str,'^.*?(\d+).*?([A-Z0-9/]+).*?(\d+).*?(\w+).*?(\d+).*?$', 1, LEVEL, 'm', 1)) LC,
regexp_substr( t.str,'^.*?(\d+).*?([A-Z0-9/]+).*?(\d+).*?(\w+).*?(\d+).*?$', 1, LEVEL, 'm', 2) BILL,
to_number(regexp_substr( t.str,'^.*?(\d+).*?([A-Z0-9/]+).*?(\d+).*?(\w+).*?(\d+).*?$', 1, LEVEL, 'm', 3)) LAST_BILL,
regexp_substr( t.str,'^.*?(\d+).*?([A-Z0-9/]+).*?(\d+).*?(\w+).*?(\d+).*?$', 1, LEVEL, 'm', 4) PAYMENT_BY,
to_number(regexp_substr( t.str,'^.*?(\d+).*?([A-Z0-9/]+).*?(\d+).*?(\w+).*?(\d+).*?$', 1, LEVEL, 'm', 5)) STATUS
from t
CONNECT BY regexp_substr( t.str,'^.*?(\d+).*?([A-Z0-9/]+).*?(\d+).*?(\w+).*?(\d+).*?$', 1, LEVEL, 'm') IS NOT NULL;
8 rows affected
select t.lc,
count(case when t.payment_by = 'C' THEN 1 else NULL end ) as count_c,
count(case when t.payment_by <> 'C' THEN 1 else NULL end ) as count_not_c
from table2 t
group by t.lc
having count(case when t.payment_by <> 'C' THEN 1 else NULL end ) < 1
| LC | COUNT_C | COUNT_NOT_C |
|---|---|---|
| 10048 | 1 | 0 |
| 10071 | 1 | 0 |