Solved

SQL join error, what am i missing

D
Rank
Badge +2
  • DavMo
  • Do Gooder (Customer)
  • 3 replies

Hi All, i have a SQL query to join 2 simple datasets, i am receiving an error saying 

"PROD_STRUCT_ALTERNATE_CFV"."PART_NO": invalid identifier

Failed executing statement (ORA-00904: "PROD_STRUCT_ALTERNATE_CFV"."PART_NO": invalid identifier

relates ot the ON part of the statement.

If anyone can help i would appreciate it.

the basic select query was working before i modified it to include the join from the other table.

SQL is as below

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.part_no_cfv = prod_struct_alternate_cfv.part_no )

 

icon

Best answer by Ozgun BAL 23 May 2022, 14:57

View original

5 replies

Ozgun BAL
Rank
Userlevel 4
Badge +8

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

 

D
Rank
Badge +2

@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 )

 

Ozgun BAL
Rank
Userlevel 4
Badge +8

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

 

D
Rank
Badge +2

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 

T
Rank
Badge +2

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

Phil

Reply


Terms & ConditionsCookie settings