CODEWITHSUNDEEP
sqlteradata
user3901666
user3901666

Reputation: 409

Two qualify clause in same statement in teradata

I have a requirement where in think i need two qualify clause in same statement.

I need two column one with correct iD's (duplicate_cnt=1) and other column with all other ids( dup_cnt>1). Based on these values i have to update another table. Is this possible in teradata?

This is my table:

Name    Description  Code   Data_Source
A   XX  101 SCRM
A   XX  102 SCRM
A   XX  103 SCRM
A   XX  104 SCRM
A   XX  105 SCRM
A   XX  110 BSCRM

This is what i want:

Name    Description Correct_code    Incorrect code  Data_Source
A   XX  101 102 SCRM
A   XX  101 103 SCRM
A   XX  101 104 SCRM
A   XX  101 105 SCRM

Thanks, AB

Upvotes: 0

Views: 3399

Answers (1)

dnoeth
dnoeth

Reputation: 60482

How do you define the "correct" code, the lowest value?

Use FIRST_VALUE to get the lowest value and a Cumulative Count to find the additional codes:

SELECT 
   Name
   ,Description
   ,First_Value(Code) -- lowest code
    Over (PARTITION BY Data_Source --, Name, Description?
          ORDER BY code) AS Correct_code
   ,code AS Incorrect_code
   ,Data_Source
FROM dropme
QUALIFY
   Count(*) -- only return rows with additional codes
   Over (PARTITION BY Data_Source --, Name, Description?
         ORDER BY code
         ROWS Unbounded Preceding) > 1

Edit:

As long as the order & the data column are the same you can also use a simple

   ,MIN(Code) -- lowest code
    Over (PARTITION BY Data_Source --, Name, Description?
         ) AS Correct_code

Upvotes: 1

Related Questions