0

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.

1
  • I'm finding it difficult to follow your logic - could you please provide some sample data and your desired result? You could use a fiddle - no Teradata I'm afraid but PostgreSQL would probably do for an explanation!
    – Vérace
    Dec 11 '20 at 12:07
0

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!

0

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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.