If you want to do the implicit join between the table and the nested collection, you need to reference the nested collection using the alias that you used for the table. Otherwise the top-level table and the nested collection are treated as independent table references and the query means "return the cartesian product of the tables". I.e. you want to rewrite as follows: select rta.transaction_purchase_id, rta.cigarette_transaction_flag, rta.non_cig_merch_transaction_flag, bow.item
from wdl_atomic.retail_transaction_attribute rta,
rta.retail_offering_material_group_distinct_list bow
where rta.fiscal_period_id = 2019001; That will solve your issue.
... View more
