CODEWITHSUNDEEP
teradataqualifiers
ckp
ckp

Reputation: 593

How to exclude the records Using Qualify statement in Teradata

I have to create population for the people who has only one product association (ABC) using qualify statement.

For example I have the data

Id  Code  Prod  Date
101  202   ABC   2017-05-31
101  203   DEF   2017-04-30
102  302   ABC   2018-06-30

From the above data I need the data for Id=102 because this id has only one prod relation where as id 101 has both ABC and DEF which should be excluded.

I tried the following

 Select id,prod from table1
 Qualify row_number() over (partition by id order by Date)=1
Where prod=‘ABC’

With this, I get the two records in my data which I don’t want. Appreciate your help.

Upvotes: 0

Views: 1103

Answers (3)

dnoeth
dnoeth

Reputation: 60482

Select *
from table1
Qualify min(Prod) over (partition by id)='ABC'
    and max(Prod) over (partition by id)='ABC'

Both MIN and MAX return the same value ABC, thus there's no other value

Upvotes: 2

Andrew
Andrew

Reputation: 8758

Just use having, instead of qualify. I don't see any need for window fuctions. Something like:

Select id,prod ,
count(prod)
from
table1
group by 
id,
prod
having count(prod) = 1

Upvotes: 0

ravioli
ravioli

Reputation: 3833

If you want to return the id's that have one prod value (ABC) in the table, you can do something like this:

SELECT id, prod
FROM (
  SELECT id, prod
  FROM table1
  GROUP BY id, prod -- Get unique (id, prod) combinations
  QUALIFY COUNT(prod) OVER(PARTITION BY id) = 1 -- Get id's with only one prod
) src
WHERE prod = 'ABC' -- Only get rows with "ABC" prod

The key here is the order in which Teradata processes the query:

  1. Aggregate - GROUP BY
  2. OLAP - COUNT(prod) OVER()
  3. QUALIFY

You may be able to move the WHERE prod = 'ABC' into the QUALIFY clause and get rid of the outer SELECT, not 100% sure.

Upvotes: 0

Related Questions