Skip to main content
Solved

SQL join error, what am i missing

D
Do Gooder (Customer)
Forum|alt.badge.img+3
  • 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

1SELECT 
2objid,
3objkey,
4bom_type,
5alternative_no,
6contract,
7part_no,
8cf$_total_plastic_weight,
9cf$_total_regrind_weight
10weight_net
11
12FROM 
13
14((SELECT 
15objid,
16objkey,
17bom_type,
18alternative_no,
19contract,
20part_no,
21cf$_total_plastic_weight,
22cf$_total_regrind_weight
23FROM prod_struct_alternate_cfv WHERE alternative_no ='*' AND bom_type ='Manufacturing' )
24
25LEFT JOIN (SELECT part_no, weight_net FROM part_catalog where part_no is not null)
26
27ON part_catalog.part_no_cfv = prod_struct_alternate_cfv.part_no )

 

Best answer by Ozgun BAL

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

 

View original
Did this topic help you find an answer to your question?

5 replies

O
Hero (Customer)
Forum|alt.badge.img+10
  • Ozgun BAL
  • Hero (Customer)
  • 102 replies
  • May 23, 2022

Hello @DavMo

 

1part_catalog.part_no_cfv

should be = part_catalog.part_no

or part_catalog_cfv.part_no

 

I think.

 

Kind Regards

Özgün Bal

 

Kind Regards
Chamika Dilhani
D
2 people like this
  • Chamika Dilhani
    Chamika Dilhani
  • D
    DavMo
D
Do Gooder (Customer)
Forum|alt.badge.img+3
  • DavMo
  • Author
  • Do Gooder (Customer)
  • 3 replies
  • May 23, 2022

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

1SELECT 
2objid,
3objkey,
4bom_type,
5alternative_no,
6contract,
7part_no,
8cf$_total_plastic_weight,
9cf$_total_regrind_weight
10weight_net
11
12FROM 
13
14((SELECT 
15objid,
16objkey,
17bom_type,
18alternative_no,
19contract,
20part_no,
21cf$_total_plastic_weight,
22cf$_total_regrind_weight
23FROM prod_struct_alternate_cfv WHERE alternative_no ='*' AND bom_type ='Manufacturing' )
24
25LEFT JOIN (SELECT part_no, weight_net FROM part_catalog where part_no is not null)
26
27ON part_catalog_cfv.part_no = prod_struct_alternate_cfv.part_no )

 

O
Hero (Customer)
Forum|alt.badge.img+10
  • Ozgun BAL
  • Hero (Customer)
  • 102 replies
  • Answer
  • May 23, 2022

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

 

Kind Regards
D
1 person likes this
  • D
    DavMo
D
Do Gooder (Customer)
Forum|alt.badge.img+3
  • DavMo
  • Author
  • Do Gooder (Customer)
  • 3 replies
  • May 23, 2022
Ozgun BAL wrote:

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 

O
1 person likes this
  • O
    Ozgun BAL
T
Do Gooder (Customer)
Forum|alt.badge.img+2
  • Tech Phil
  • Do Gooder (Customer)
  • 3 replies
  • November 21, 2022

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

Phil

Reply


Terms & ConditionsCookie settingsAccessibility statement

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings