Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create Table ACCOUNT_DETAILS (
TRX_ID int,
TYPE char(4),
FAC_ID char(6),
ACC_ID char(6));
insert into ACCOUNT_DETAILS
SELECT 1234,'CRDR','ABC123','AB1234' FROM DUAL UNION ALL
SELECT 1234,'DBTR','XYZ222','XY9800' FROM DUAL UNION ALL
SELECT 9876,'CRDR','EFG999','EF7659' FROM DUAL UNION ALL
SELECT 9876,'DBTR','ABC123','AB9900' FROM DUAL ;
4 rows affected

select 
TRX_ID,
FAC_ID,
ACC_ID,
CASE WHEN TYPE = 'DBTR' AND FAC_ID LIKE 'ABC%' THEN 0
      WHEN TYPE = 'CRDR' AND FAC_ID LIKE 'ABC%' THEN 0 
	  ELSE 1 END ordinal
FROM ACCOUNT_DETAILS


TRX_ID
FAC_ID
ACC_ID
ORDINAL


1234
ABC123
AB1234
0

1234
XYZ222
XY9800
1

9876
EFG999
EF7659
1

9876
ABC123
AB9900
0


with cte as 
(
select 
TRX_ID,
FAC_ID,
ACC_ID,
CASE WHEN TYPE = 'DBTR' AND FAC_ID LIKE 'ABC%' THEN 0
      WHEN TYPE = 'CRDR' AND FAC_ID LIKE 'ABC%' THEN 0 
	  ELSE 1 END ordinal
FROM ACCOUNT_DETAILS)
SELECT
TRX_ID,
FAC_ID,
ACC_ID
FROM CTE
WHERE ordinal = 1;


TRX_ID
FAC_ID
ACC_ID


1234
XYZ222
XY9800

9876
EFG999
EF7659


with cte as 
(
select 
TRX_ID,
FAC_ID,
ACC_ID,
CASE WHEN FAC_ID LIKE 'ABC%' THEN 0 ELSE 1 END ordinal
FROM ACCOUNT_DETAILS)
SELECT
TRX_ID,
FAC_ID,
ACC_ID
FROM CTE
WHERE ordinal = 1;


TRX_ID
FAC_ID
ACC_ID


1234
XYZ222
XY9800

9876
EFG999
EF7659

TRX_ID	FAC_ID	ACC_ID	ORDINAL
1234	ABC123	AB1234	0
1234	XYZ222	XY9800	1
9876	EFG999	EF7659	1
9876	ABC123	AB9900	0