What does this index doing in the select statement

Database

What does this index doing in the select statement

Hello,

I was wondering what does this index function in the select statement?

Thanks

select
data.GCN_NBR,
b.brand_nme,
b.conversion_rate,
data.INFERRED_FILL_METHOD_CDE,
sum(index('R',mail_retail_cde)) as R_Rx,
sum(index('R',mail_retail_cde)*index('B',INFERRED_FILL_METHOD_CDE)*Rx) as R_G_Eq_Rx,
sum(index('R',mail_retail_cde)*index('A',INFERRED_FILL_METHOD_CDE)*Rx*coalesce(b.conversion_rate,0)) as R_G_Alt_Rx,
sum(index('M',mail_retail_cde)*Rx) as M_Rx,
sum(index('M',mail_retail_cde)*index('B',INFERRED_FILL_METHOD_CDE)*Rx) as M_G_Eq_Rx,
sum(index('M',mail_retail_cde)*index('A',INFERRED_FILL_METHOD_CDE)*Rx*coalesce(b.conversion_rate,0)) as M_G_Alt_Rx

from
mwad_workdb.data1_Karnik data
left outer join mwad_workdb.pairs_&label
b on data.gcn_nbr = b.gcn_nbr

group by 1,2,3,4) with data primary index(GCN_NBR)
1 REPLY
Senior Apprentice

Re: What does this index doing in the select statement

INDEX(b,a) is outdated Teradata SQL for POSITION(a in b)

Strange logic, i would rewrite
sum(index('R',mail_retail_cde))
to
sum(case when mail_retail_cde = 'R' then 1 else 0 end)

Dieter