how to select the not null value of a column when it has both null and not null value in teradata?

Question

consider a table named "TABLE-A" with below data.

ID   hcc    D_CD
1    null   1
1    5      1
2    null   2
2    10     2
2    20     3
3    null   3

I need to select ID,hcc fields from TABLE-A in the combination of ID,D_CD. when the combination has single hcc value (even it is null) it should be fetched. But, when it has both null and not null values, it should fetch the not null value only. So the output must be as below.

OUTPUT:

ID   hcc
1     5
2     10
2     20
3     null

can anyone please help me on this? thanks in advance.

Answer 1

What I did to solve this was:

CREATE TABLE x 
(
  id SMALLINT NOT NULL, 
  hcc SMALLINT NULL, 
  d_cd SMALLINT NOT NULL
);

and populate it:

INSERT INTO x VALUES
(1,    null,   1),
(1,    5   ,  1),
(2,    null,  2),
(2,    10,     2),
(2,    20,     3),
(3,    null,   3);

And then run this query:

WITH y AS
(
  SELECT id, hcc
  FROM x
  WHERE hcc IS NOT NULL
),
z AS
(
  SELECT id, hcc
  FROM x
  WHERE hcc IS NULL AND id NOT IN (SELECT id FROM y)
)
SELECT id, hcc FROM y
UNION
SELECT id, hcc FROM z
ORDER BY id;

Result:

ID   hcc
1     5
2     10
2     20
3     null

As desired!

In future, could you please provide a fiddle with your table DDL and DML! This should work with Teradata - I double-checked and it does support Common Table Expressions!

Answer 2

Based on your description it's a simple aggregation:

select id, max(hcc)
from x
group by id, d_cd
;

If you want additional columns you can apply the same logic using an OLAP-function:

select *
from x
qualify
   rank()
   over (partition by id, d_cd 
         order by hcc nulls last) = 1
;

If there are multiple non-NULL values and you want to return them all:

select *
from x
qualify
   rank()
   over (partition by id, d_cd 
         order by case when hcc is not null then 0 else 1 end) = 1
;