When I have multple values a column, I would like to display the static value 'More than one Value' else I would like the original column value to be retained.

This gets me what I want but this limits the results to the HAVING COUNT(B.COL3) > 1 condition:

SELECT
    A.COL1,
    A.COL2,
    'More than one Value' AS COL3
FROM TBL1 A
RIGHT JOIN TBL2 B ON A.TBL1-ID = B.TBL2-ID
GROUP BY A.COL1, A.COL2
HAVING COUNT(B.COL3) > 1;

This one is failing:

SELECT
    A.COL1,
    A.COL2,
    CASE WHEN COUNT (B.COL3) >1 THEN 'More than one Value' ELSE B.COL3 END AS COL3
FROM TBL1 A
RIGHT OUTER JOIN TBL2 B ON A.TBL1-ID = B.TBL2-ID
GROUP BY A.COL1, A.COL2;

Here is how is a mockup of the data

CodePudding user response：

Assuming B.COL3 is a string then:

SELECT A.COL1,
       A.COL2,
       CASE
       WHEN COUNT(B.COL3) > 1
       THEN 'More than one Value'
       ELSE MAX(B.COL3)
       END AS COL3
FROM   TBL1 A
       RIGHT JOIN TBL2 B
       ON A.TBL1_ID = B.TBL2_ID
GROUP BY A.COL1, A.COL2;

If it is not a string then use TO_CHAR(MAX(B.col3)).

Which, for the sample data:

CREATE TABLE tbl1 (col1, col2, tbl1_id) AS
SELECT 'A',  'B',  1 FROM DUAL UNION ALL
SELECT 'A1', 'B1', 2 FROM DUAL;

CREATE TABLE tbl2 (col3, tbl2_id) AS
SELECT 'C',  1 FROM DUAL UNION ALL
SELECT 'C1', 2 FROM DUAL UNION ALL
SELECT 'C2', 2 FROM DUAL;

Outputs:

fiddle