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

plsqlteradata

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.

Best Answer

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!

Related Question