I have the below table:
create table t1(col1, col2, col3, col4 )
as
select 0,1,'ABC',1 FROM DUAL
UNION ALL
select 0,1,'ABC',2 FROM DUAL
UNION ALL
select 0,1,'ABC',3 FROM DUAL
UNION ALL
select 0,2,'ABC',1 FROM DUAL
UNION ALL
select 0,2,'ABC',2 FROM DUAL
UNION ALL
select 0,2,'ABC',3 FROM DUAL
UNION ALL
select 0,3,'ABC',1 FROM DUAL
UNION ALL
select 0,4,'ABC',1 FROM DUAL
UNION ALL
select 0,5,'ABC' ,1 FROM DUAL
UNION ALL
select 1,11,'DEF',1 FROM DUAL
UNION ALL
select 1,11,'DEF',2 FROM DUAL
UNION ALL
select 1,11,'DEF',3 FROM DUAL
UNION ALL
select 1,12,'DEF',1 FROM DUAL
UNION ALL
select 1,12,'DEF',2 FROM DUAL
UNION ALL
select 1,12,'DEF',3 FROM DUAL
UNION ALL
select 1,13,'DEF',1 FROM DUAL
UNION ALL
select 1,13,'DEF',2 FROM DUAL
UNION ALL
select 1,13,'DEF',3 FROM DUAL
UNION ALL
select 11,111,'XYZ',1 FROM DUAL
UNION ALL
select 11,111,'XYZ',2 FROM DUAL
UNION ALL
select 11,111,'XYZ',3 FROM DUAL
UNION ALL
select 11,112,'XYZ',1 FROM DUAL
UNION ALL
select 11,112,'XYZ',2 FROM DUAL
UNION ALL
select 11,112,'XYZ',3 FROM DUAL
UNION ALL
select 11,113,'GHI',1 FROM DUAL
UNION ALL
select 11,113,'GHI',2 FROM DUAL
UNION ALL
select 11,113,'GHI',3 FROM DUAL;
And I want the data like below:
| COL1 | COL2 | COL3 | COL4 |
|---|
| 11 | 111 | XYZ | 3 |
| 11 | 112 | XYZ | 3 |
I have started off something like below and is not working:
WITH DATA0(COL1, COL2, COL3, COL4) AS
(
SELECT COL1, COL2, COL3, max(col4) FROM T1 WHERE COL1 = :user_value --In this case it is 0 (zero)
group by col1, col2, COL3
UNION ALL
SELECT T1.COL1, T1.COL2, T1.COL3, MAX(T1.COL4) FROM DATA0, T1
WHERE DATA0.COL2 = T1.COL1
)
SELECT * FROM DATA0
WHERE COL3 = 'XYZ';
Error:
ORA-32486: unsupported operation in recursive branch of recursive WITH clause
Kindly help me. Thanks in advance.