Lateral View Outer Explode: Results in Rows Multipliers

[#1]

Hi

I am struggling with lateral view outer explode statement. This is the structure of my table:

TBL_CUSTOMER

CustId                                                                          string

Cust_Resident_Country                                               array

                Item                                                             string

Cust_TaxIdenitity                                                        array

                Item                                                           struct

                                Tin_issuing_country                    string

                                Tin                                             string

CustId   Cust_Resident_Country                               Cust_TaxIdenitity

1              [AU:HK]                                                               [{AU:987678;HK:76556}{ AU:987678;HK:76556}]

When I use the lateral view outer explode for this object I get 4 rows for the above row:

1              HK                                                          AU                          987678

1              HK                                                          HK                          765556

1              AU                                                          AU                          987678

1              AU                                                          HK                          76556

The desired result is :

1              HK                                                          AU                          987678

1              AU                                                          HK                          76556

And there are around 4 more array columns that have to be exploded.How do I stop the mapping of this UDTF mapping to each element of the array resulting in multiplication in row output.

Thanks!

Samir

[#2]

Hi,

Can you please share your query that has the LATERAL VIEW? This can help to see if you have any issues on your query.

[#3]

Hi

Thank you for your response.

I am running it for one account number

select distinct msg_bet_id
, atchd_doc_ref_id
, idv_cd as Resident_Country_Code
, idv_tin.tin as TIN
, idv_tin.tin_issg_cntry_cd as TIN_Country_Code
FROM tt17.crs_account_report a1
lateral view outer explode(hldr_idv_rsdnt_cntry_cds)exp1 as idv_cd
lateral view outer explode(hldr_idv_tins)exp2 as idv_tin

where accountnumber=123