ORACLE - Return NULL if values from a column are different-CodePudding

Please help me to create a script for next scenario:

There are credit applications and each credit has sub-products and each sub-product has a score. Ex. Credit application (Credit_ID) 11111 has four sub-products (Facil_ID) and for all of them the score is the same 'G' (Score_Type)

Credit application 22222 has four sub-products and the score are different for them, some of them has 'G' some of them 'B' some 'Z'. Put details on the picture.

Request: if SCORE_TYPE is the same for all of sub-products then SCORE_TYPE = 'G', if there are different scores at sub-products level, then NULL.

CREDIT_ID	FACIL_ID	SCORE_TYPE
11111	23	G
11111	25	G
11111	26	G
11111	27	G
22222	67	G
22222	68	B
22222	69	B
22222	70	Z

Expected Output:

CREDIT_ID	SCORE_TYPE
11111	G
22222	NULL

My idea was to use

CASE 
    WHEN  COUNT(DISTINCT SCORE_TYPE) > 1 THEN null
    ELSE  SCORE_TYPE 
END AS SCORE_TYPE

But looks I'm wrong.

Any idea?

CodePudding user response：

We can try aggregation here:

SELECT Credit_ID,
       CASE WHEN MIN(Score_Type) = MAX(Score_Type) THEN 'G' END AS Score_Type
FROM yourTable
GROUP BY Credit_ID;

If, for a given credit IT, the smallest and largest score type be identical, then it implies that there is only one score type value.

CodePudding user response：

You can use your code, but wrap the ELSE condition in an aggregation function:

SELECT credit_id,
       CASE 
       WHEN COUNT(DISTINCT score_type) > 1
       THEN NULL
       ELSE MAX(score_type)
       END AS score_type
FROM   table_name
GROUP BY credit_id
ORDER BY credit_id;