Multi-Part Identifier

[bdmoran]

Hello,

I keep receiving this error:

Msg 207, Level 16, State 1, Line 7
Invalid column name 'acct_id'.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "v_returned_inventory.repid" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "v_returned_inventory.acct_rep_id" could not be bound.

Here is my SQL:

SELECT AT_trans_for_emailTrigger.email_addr,AT_trans_for_emailTrigger.inet_event_description,Convert(nvarchar(50), v_live_inventory.event_date,(101)) as event_date,right(CONVERT(varchar, v_live_inventory.event_time, 100),7) as event_time,AT_trans_for_emailTrigger.add_usr,
sum(v_live_inventory.num_seats) as num_seats, v_live_inventory.section_name, v_live_inventory.acct_id, premclub.name_first + ' ' + premclub.name_last AS name,v_returned_inventory.acct_rep_id

FROM AT_trans_for_emailTrigger INNER JOIN
v_live_inventory ON AT_trans_for_emailTrigger.orderNumber = v_live_inventory.other_info_1 INNER JOIN
OPENQUERY(PREMCLUB, 'select name_first, name_last, cust_name_id from dba.v_cust_name') AS premclub ON
AT_trans_for_emailTrigger.cust_name_id = premclub.cust_name_id LEFT OUTER JOIN OPENQUERY(PREMCLUB,'select email_addr from dba.v_cust_email')as repemail ON repemail.acct_id = v_returned_inventory.acct_rep_id LEFT OUTER JOIN
v_cust_phone ON AT_trans_for_emailTrigger.cust_name_id = v_cust_phone.cust_name_id
GROUP BY AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.inet_event_description, v_live_inventory.event_date,
v_live_inventory.event_time, v_live_inventory.section_name, v_live_inventory.acct_id,
premclub.name_first + ' ' + premclub.name_last,AT_trans_for_emailTrigger.add_usr



Usually when I receive this error this is some sort of typo, but I double checked and everything is spelled correctly. Any other ideas?

Thanks!

 

[jrbarnett]

I think

LEFT OUTER JOIN OPENQUERY(PREMCLUB,'select email_addr from db[u]a[/u].v_cust_email')as repemail

should be

LEFT OUTER JOIN OPENQUERY(PREMCLUB,'select email_addr from db[u]o[/u].v_cust_email')as repemail

However it could be owned by a schema named dba - there is nothing to stop you having this.
with the same change for

OPENQUERY(PREMCLUB, 'select name_first, name_last, cust_name_id from db[u]a[/u].v_cust_name') AS premclub

John

 

[bdmoran]

No, that's not it. I know it should be dba -- I tried to switch it regardless but no luck.

 

[kray4660]

This query is a little bit more complicated that what I am used to seeing (I have never used OPENQUERY), but I did not see the v_returned_inventory table in the from clause anywhere (except when trying to use a field from it).

 

[fredericofonseca]

two errors.

1-
v_returned_inventory is neither a table within the query, neither an alias - either you got the wrong alias, or you are missing a table.

2-
LEFT OUTER JOIN OPENQUERY(PREMCLUB,'select email_addr from dba.v_cust_email')as repemail
ON repemail.acct_id = v_returned_inventory.acct_rep_id
As you do not have acct_id on the select list of the openquery this is also in error. You need to return the field that represents that value from the openquery and name it acct_id if that is not the real name on your Sybase table

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bdmoran]

Frederico - Thanks for your response. I've added v_returned_inventory.acct_rep_id to the select statement but I still receive the error. I've also tried to add it to the group by clause.

 

[fredericofonseca]

show us the changed code please

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bdmoran]

SELECT AT_trans_for_emailTrigger.email_addr,AT_trans_for_emailTrigger.inet_event_description,Convert(nvarchar(50), v_live_inventory.event_date,(101)) as event_date,right(CONVERT(varchar, v_live_inventory.event_time, 100),7) as event_time,AT_trans_for_emailTrigger.add_usr,
sum(v_live_inventory.num_seats) as num_seats, v_live_inventory.section_name, v_live_inventory.acct_id,v_returned_inventory.rep_acct_id, premclub.name_first + ' ' + premclub.name_last AS name,v_returned_inventory.acct_rep_id

FROM AT_trans_for_emailTrigger INNER JOIN
v_live_inventory ON AT_trans_for_emailTrigger.orderNumber = v_live_inventory.other_info_1 INNER JOIN
OPENQUERY(PREMCLUB, 'select name_first, name_last, cust_name_id from dba.v_cust_name') AS premclub ON
AT_trans_for_emailTrigger.cust_name_id = premclub.cust_name_id LEFT OUTER JOIN OPENQUERY(PREMCLUB,'select acct_id,email_addr from dba.v_cust_email')as repemail ON repemail.acct_id = v_returned_inventory.acct_rep_id LEFT OUTER JOIN
v_cust_phone ON AT_trans_for_emailTrigger.cust_name_id = v_cust_phone.cust_name_id
GROUP BY AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.inet_event_description, v_live_inventory.event_date,
v_live_inventory.event_time, v_live_inventory.section_name, v_live_inventory.acct_id,
premclub.name_first + ' ' + premclub.name_last,AT_trans_for_emailTrigger.add_usr,v_returned_inventory.rep_acct_id

 

[fredericofonseca]

Looks like you miusderstood what I said..

1-
v_returned_inventory is neither a table within the query, neither an alias - either you got the wrong alias, or you are missing a table.

This means that all your fields that have v_returned_inventory are incorrect as v_returned_inventory is not a table (or table alias) of your SQL.


The only "good" change you did and which fixed one of the errors was adding acct_id to the select list of the repemail sql which sorted one of your problems.

The other problem you didn't fix. you only added another field to the select list which references the invalid table

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[SQLBill]

I cleaned up your code to try and show you what Frederico is trying to explain to you. This is the FROM of your statement and has to include all the tables or table aliases being used. The highlighted parts are the tables/aliases. Can you show us a table or a table alias called v_returned_inventory anywhere in this FROM list?

FROM [highlight #FCE94F]AT_trans_for_emailTrigger[/highlight] 
  INNER JOIN [highlight #FCE94F]v_live_inventory[/highlight] 
   ON AT_trans_for_emailTrigger.orderNumber = v_live_inventory.other_info_1 
 INNER JOIN
OPENQUERY(PREMCLUB, 'select name_first, name_last, cust_name_id from dba.v_cust_name') AS [highlight #FCE94F]premclub[/highlight] 
  ON AT_trans_for_emailTrigger.cust_name_id = premclub.cust_name_id 
 LEFT OUTER JOIN OPENQUERY(PREMCLUB,'select acct_id,email_addr from dba.v_cust_email')[highlight #FCE94F]as repemail[/highlight] 
   ON repemail.acct_id = v_returned_inventory.acct_rep_id 
 LEFT OUTER JOIN [highlight #FCE94F]v_cust_phone[/highlight]    
   ON AT_trans_for_emailTrigger.cust_name_id = v_cust_phone.cust_name_id

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875