By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE /*app_lco.*/tbl_fip_checklist (linkid, mz_code, mz_name, mho_handover_cert, created_date, status) AS
SELECT 'AAAAAAAAA', 'B', 'C', 'abc', DATE '1970-01-01', 'APPROVED' FROM DUAL UNION ALL
SELECT 'AAAAAAAAA', 'B', 'C', '1' , DATE '1970-01-01', 'APPROVED' FROM DUAL UNION ALL
SELECT 'AAAAAAAAA', 'B', 'C', '2' , DATE '1970-01-01', 'APPROVED' FROM DUAL UNION ALL
SELECT 'AAAAAAAAA', 'B', 'C', '3e1', DATE '1970-01-01', 'APPROVED' FROM DUAL;
4 rows affected
SELECT linkid AS spanid,
mz_code AS maint_zone_code,
mz_name AS maint_zone_name,
SUM(TO_NUMBER(mho_handover_cert DEFAULT NULL ON CONVERSION ERROR))
AS ne_length,
TRUNC(created_date) AS offered_date
FROM /*app_lco.*/tbl_fip_checklist
WHERE LENGTH(TRIM(linkid)) > 8
AND LENGTH(TRIM(linkid)) < 21
AND status = 'APPROVED'
GROUP BY
linkid,
mz_code,
mz_name,
TRUNC(created_date);
| SPANID | MAINT_ZONE_CODE | MAINT_ZONE_NAME | NE_LENGTH | OFFERED_DATE |
|---|---|---|---|---|
| AAAAAAAAA | B | C | 33 | 1970-01-01 00:00:00 |