Oracle 21c

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 your_table_name (member_code, forename, surname, date_of_birth) AS
SELECT 1, 'Ng',     'Ng',     DATE '2010-01-01' FROM DUAL UNION ALL
SELECT 2, 'Thomas', 'Thomas', DATE '2008-01-01' FROM DUAL UNION ALL
SELECT 3, 'Alice',  'Abbot',  DATE '1970-01-01' FROM DUAL;
3 rows affected

SELECT member_code,
       error_status
FROM   your_table_name
       CROSS JOIN LATERAL (
         SELECT 'Forename less than 3 characters' AS error_status,
                1 AS priority
         FROM   DUAL
         WHERE  LENGTH(forename) < 3
         UNION ALL
         SELECT 'Date of birth after 31/01/2008' AS error_status,
                1 AS priority
         FROM   DUAL
         WHERE  date_of_birth > DATE '2008-01-31'
         UNION ALL
         SELECT 'Forename is equal to surname' AS error_status,
                1 AS priority
         FROM   DUAL
         WHERE forename = surname
         UNION ALL
         SELECT 'No Error' AS error_status,
                2 AS priority
         FROM   DUAL
         ORDER BY priority
         FETCH FIRST ROW WITH TIES
       );


MEMBER_CODE
ERROR_STATUS


1
Forename less than 3 characters

1
Date of birth after 31/01/2008

1
Forename is equal to surname

2
Forename is equal to surname

3
No Error


SELECT member_code,
       error_status
FROM   (
  SELECT member_code,
         err1,
         err2,
         err3,
         CASE 
         WHEN err1 IS NULL AND err2 IS NULL AND err3 IS NULL
         THEN 'No Error'
         END AS no_error
  FROM   (
    SELECT member_code,
           CASE WHEN LENGTH(forename) < 3 THEN 'Forename less than 3 characters' END
             AS err1,
           CASE WHEN date_of_birth > DATE '2008-01-31' THEN 'Date of birth after 31/01/2008' END
             AS err2,
           CASE WHEN forename = surname THEN 'Forename is equal to surname' END
             AS err3
    FROM   your_table_name
  )
)
UNPIVOT (error_status FOR type IN (err1, err2, err3, no_error));


MEMBER_CODE
ERROR_STATUS


1
Forename less than 3 characters

1
Date of birth after 31/01/2008

1
Forename is equal to surname

2
Forename is equal to surname

3
No Error

MEMBER_CODE	ERROR_STATUS
1	Forename less than 3 characters
1	Date of birth after 31/01/2008
1	Forename is equal to surname
2	Forename is equal to surname
3	No Error