How to group records based on mask?

Analytics

How to group records based on mask?

Hi there 

 

Looking for some sugestion to group records like this:

 

LEDG_ACC_NO    LEDG_SUB_ACC_NO     AMOUNT
-----------                 ---------------                        ------
123                       111                                     10.00
123                       1_1                                     5.00
123                       222                                     20.00

 

Into 

 

LEDG_ACC_NO    LEDG_SUB_ACC_NO     AMOUNT 
-----------                 ---------------                        ------ 
123                       1_1                                     15.00
123                       222                                     20.00

 

Join:

on a.ledg_acc_no = b.ledg_acc_no
and b.ledg_sub_acc_no like a.ledg_sub_acc_no;

 

Any help appreciated.

Thanks

3 REPLIES
Teradata Employee

Re: How to group records based on mask?

Join has nothing to do with this - it's just an aggregation. If you are thinking of joining the table to itself, I'm not sure how you would write that without getting a product join.  Perhaps if you knew all the sub_acc_no's that contain '_' you could write a complex CASE expression that would return a field you could group by, such as:

 

Select ledg_acc_no

    ,CASE ledg_sub_acc_no

        when '111' then '1_1'

        when '121' then '1_1'

        when '131' then '1_1'

        when '112' then '1_2'

        <etc.>

        else ledg_sub_acc_no

      END as agg_ledg_sub_acc_no

    ,SUM(amount)

group by 1, 2

Teradata Employee

Re: How to group records based on mask?

If the rule is that you always want to compare the first and third characters in the ledg_sub_acc_no column, then you can use the substring function. I've replaced the middle value of this string with * to denote any value. 

 

DROP TABLE test_db.mt_test;
CREATE TABLE test_db.mt_test (
	id INT
	,ledg_acc_no INT
	,ledg_sub_acc_no CHAR(3)
	,amount NUMERIC(5,2)
) PRIMARY INDEX(id);

INSERT INTO test_db.mt_test (1,123,'111',10.00);
INSERT INTO test_db.mt_test (2,123,'1_1',5.00);
INSERT INTO test_db.mt_test (3,123,'222',20.00);

SELECT * FROM test_db.mt_test;

--id	ledg_acc_no	ledg_sub_acc_no	amount
--1	123	        111   		10.00
--2	123	        1_1   		5.00
--3	123	        222   		20.00


SELECT
	ledg_acc_no
	,SUBSTR(ledg_sub_acc_no,1,1) 
		|| '*'
		|| SUBSTR(ledg_sub_acc_no,3,1) as ledg_sub_acc_no_group
	, SUM(amount)
FROM test_db.mt_test
GROUP BY ledg_acc_no, ledg_sub_acc_no_group

--ledg_acc_no	ledg_sub_acc_no_group	amount
--123		1*1			15.00
--123		2*2			20.00

 

 

Highlighted

Re: How to group records based on mask?

Thanks for your reply but both solution are not flexible enough for my solution.

I don't know all ledg_acc_no as well as mask is not always on 3rd place.

This is defined by businnes in dictionary and can change anytime.

 

I did it by joining records with mask 

 

where REGEXP_SIMILAR(ledg_sub_acc_no, '[0-9]+_[0-9]+') = 1

 

to records without mask

 

where AND REGEXP_SIMILAR(ledg_sub_acc_no, '[0-9]*') = 1  

 

and then adding values

 

ZEROIFNULL(AA.Amount) + ZEROIFNULL(AB.Amount) SUM_Amount

 

It is working fine for last few days and is flexible for bussines.