insert fails with distinct
INSERT INTO ihub.tmp_component_substitutes(
usage_quantity,
priority,
organization_code,
assembly_name,
component_name,
operation_seq_code,
effectivity_date,
sub_item_name,
alternate_bom_designator,
sr_instance_code,
deleted_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
PROCESS_FLAG
)
SELECT DISTINCT
(
h.qty_per * i.conversion_rate),
i.sub_priority_nbr,
'P'||':'|| a.business_unit,
h.inv_item_id,
h.component_id,
CASE WHEN h.op_sequence = 0 THEN 1 ELSE h.op_sequence END,
i.effdt_to,
i.sub_itm_id,
CASE WHEN f.bom_code = ii.bom_code THEN NULL ELSE f.bom_code END,
usage_quantity,
priority,
organization_code,
assembly_name,
component_name,
operation_seq_code,
effectivity_date,
sub_item_name,
alternate_bom_designator,
sr_instance_code,
deleted_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
PROCESS_FLAG
)
SELECT DISTINCT
(
h.qty_per * i.conversion_rate),
i.sub_priority_nbr,
'P'||':'|| a.business_unit,
h.inv_item_id,
h.component_id,
CASE WHEN h.op_sequence = 0 THEN 1 ELSE h.op_sequence END,
i.effdt_to,
i.sub_itm_id,
CASE WHEN f.bom_code = ii.bom_code THEN NULL ELSE f.bom_code END,
0