Hello @DavMo

part_catalog.part_no_cfv

should be = part_catalog.part_no

or part_catalog_cfv.part_no

I think.

Kind Regards

Özgün Bal

@Ozgun BAL 

Good spot thank you, I corrected this now, and still receive the same error message.

I'm wondering if I have improperly bracketed but I cant get my head around it since i cant see an alternative way to go.

the code is now:

SELECT 
objid,
objkey,
bom_type,
alternative_no,
contract,
part_no,
cf$_total_plastic_weight,
cf$_total_regrind_weight
weight_net

FROM 

((SELECT 
objid,
objkey,
bom_type,
alternative_no,
contract,
part_no,
cf$_total_plastic_weight,
cf$_total_regrind_weight
FROM prod_struct_alternate_cfv WHERE alternative_no ='*' AND bom_type ='Manufacturing' )

LEFT JOIN (SELECT part_no, weight_net FROM part_catalog where part_no is not null)

ON part_catalog_cfv.part_no = prod_struct_alternate_cfv.part_no )

Hello @DavMo ,

I can’t test this query in my enviroment because we don’t have custom field in prod_struct_alternate,

But when I remove the CFVs and CF$ elements in query, I did some change with giving some aliases in the query and it seems working.

SELECT
*

FROM

(SELECT
objid,
objkey,
bom_type,
alternative_no,
contract,
part_no

FROM prod_struct_alternate WHERE alternative_no ='*' AND bom_type ='Manufacturing') Y

LEFT JOIN (SELECT part_no, weight_net FROM part_catalog where part_no is not null) X

ON X.part_no = Y.part_no

Can you test this please?

With this aliases you can add CFVs and CF$ elements which you want, I couldn’t test this because we don have custom fields in this view tables.

Kind Regards

Özgün Bal

@Ozgun BAL  This Worked perfectly, thank you so much for your help ! I only adjusted the table names to add the _cfv 

In T-SQL, I would expect to put an alias after parenthetical subqueries in the FROM clause such as “t1”