Appreciate your help on this.
I have below sql query failing with error 'ORA-01427: single-row subquery returns more than one row'
WITH t
AS (SELECT 'A' Col1, 'B' Col2, 'C' Col3 FROM DUAL
UNION
SELECT 'D', 'E', 'F' FROM DUAL
UNION
SELECT 'E', 'F', 'G' FROM DUAL
UNION
SELECT 'A', 'T', 'U' FROM DUAL),
t1
AS (SELECT 'A' Col1, 'B' Col2, 'C' Col3 FROM DUAL
UNION
SELECT 'D1', 'E1', 'F1' FROM DUAL
UNION
SELECT 'D', 'E', 'F' FROM DUAL
UNION
SELECT 'S', 'V', 'W' FROM DUAL),
t2
AS (SELECT 'A' Col1, 'B' Col2, 'C' Col3 FROM DUAL
UNION
SELECT 'A', 'E1', 'F1' FROM DUAL
UNION
SELECT 'D', 'E', 'F' FROM DUAL)
SELECT t.col1,
(SELECT distinct col2
FROM t2
WHERE t2.col1 = t.col1) RND_COL
FROM t, t1
WHERE t.col1 = t1.col1
WhenI checked the count using below query
WITH t
AS (SELECT 'A' Col1, 'B' Col2, 'C' Col3 FROM DUAL
UNION
SELECT 'D', 'E', 'F' FROM DUAL
UNION
SELECT 'E', 'F', 'G' FROM DUAL
UNION
SELECT 'A', 'T', 'U' FROM DUAL),
t1
AS (SELECT 'A' Col1, 'B' Col2, 'C' Col3 FROM DUAL
UNION
SELECT 'D1', 'E1', 'F1' FROM DUAL
UNION
SELECT 'D', 'E', 'F' FROM DUAL
UNION
SELECT 'S', 'V', 'W' FROM DUAL),
t2
AS (SELECT 'A' Col1, 'B' Col2, 'C' Col3 FROM DUAL
UNION
SELECT 'A', 'E1', 'F1' FROM DUAL
UNION
SELECT 'D', 'E', 'F' FROM DUAL)
SELECT t.col1,
(SELECT count(col2)
FROM t2
WHERE t2.col1 = t.col1) RND_COL
FROM t, t1
WHERE t.col1 = t1.col1 (+)
it shows as below
-----------------
COL1 RND_COL
A 2
A 2
D 1
E 0
My Requirement is for all the values in the column 'RND_COL' > 1 it should display text as 'Multiple', for 'RND_COL' = 1 display actual value returned by sub query and 'RND_COL' = 0 display it as NULL.
expected output
-------------------
COL1 RND_COL
A Multiple
A Multiple
D E
E NULL