Urgently require a help on a query

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Urgently require a help on a query

I have a requirement wherein for a combination of Cust_Id & Assoc_Cust_Id, if  any of the records has Product_1 = Product_2, then all records for the group should have the Result column updated as ‘Y’

Else “N”

 

How can I achieve this.? Below are some sample outputs on what I expect.

 

Query used to get the data:

 

SELECT NP .*,

UPPER(H1.PRODUCT_2)

FROM

AD.TABLE_1 NP

LEFT JOIN AD.TABLE_2 HF

ON

NP.CUSTOMER_ASSO_PRES=HF.CUSTOMER_ID

AND UPPER(PRODUCT_1)=UPPER(PRODUCT_2)

AND CAST(NP.EDATE AS DATE FORMAT 'MM/DD/YY')  + INTERVAL '100' YEAR  >= CAST(START_DATE AS DATE FORMAT 'MM/DD/YY')

AND CAST(NP.EATE AS DATE FORMAT 'MM/DD/YY') + INTERVAL '100' YEAR  < CAST(END_DATE AS DATE FORMAT 'MM/DD/YY')

--WHERE NP.ABBOTT_CUSTOMER_ASSO_PRES='498766'

WHERE NP.PROFILE_ID IS NOT NULL

AND NP.PRODUCT_1 IS NOT NULL

 

Ouputs:

Scenario : 1

  Cust_Id      Asso_Cust_Id      State_Code        EV_ID        RV_ID        PRODUCT_1         PRODUCT_2         Result
    4232            4603                D                DD2-D04-45     EOV 191        H_C_DS                        NULL                  N
    4232            5423                D                DD2-D04-45     EOV 191        H_H_DS                        NULL                  N
   

Scenario : 2

  Cust_Id      Asso_Cust_Id      State_Code        EV_ID           RV_ID        PRODUCT_1         PRODUCT_2     Result
    5790            460347                D                DD2-D04-45     EOV 191        H_C_DS                                            Y
    5790            460347                D                DD2-D04-45     EOV 191        H_H_DS                H_H_DS              Y
    5790            460347                D                DD2-D04-45     EOV 191        H_P_DS                H_P_DS              Y
    5790           460347                 D                DD2-D04-45     EOV 191        H_U_DS                                            Y


Your help immediately is much appreciated. 

 

Thanks

John

1 REPLY
Teradata Employee

Re: Urgently require a help on a query

Hi John,

 

Try like this.

Data

 

create multiset volatile table mvt_table, no log
( Cust_Id       integer
, Asso_Cust_Id  integer
, Product_1     char(10)
, Product_2     char(10)
)
primary index (Cust_Id, Asso_Cust_Id)
on commit preserve rows;

insert into mvt_table values (4232,   4603, 'H_C_DS', null    );
insert into mvt_table values (4232,   5423, 'H_H_DS', null    );
insert into mvt_table values (5790, 460347, 'H_C_DS', null    );
insert into mvt_table values (5790, 460347, 'H_H_DS', 'H_H_DS');
insert into mvt_table values (5790, 460347, 'H_P_DS', 'H_P_DS');
insert into mvt_table values (5790, 460347, 'H_U_DS', null    );

Query

select t.*
     , max(case when Product_1 = Product_2 then 'Y' else 'N' end) over(partition by Cust_Id, Asso_Cust_Id) as res
  from mvt_table as t;

 Cust_Id Asso_Cust_Id Product_1            Product_2            res 
 ------- ------------ -------------------- -------------------- --- 
    4232         4603 H_C_DS               NULL                 N  
    4232         5423 H_H_DS               NULL                 N  
    5790       460347 H_P_DS               H_P_DS               Y  
    5790       460347 H_U_DS               NULL                 Y  
    5790       460347 H_H_DS               H_H_DS               Y  
    5790       460347 H_C_DS               NULL                 Y