does not match with a table name or alias name used in the query

• David Korb

  SSCrazy

  Points: 2505

  More actions

  October 20, 2006 at 3:51 pm

  #121003

  Hi all,

  The equivalent to the following insert query was working fine until I changed the join to "right join".

  Then I started getting the error: "Statement(s) could not be prepared." "The column prefix 'ACC' does not match with a table name or alias name used in the query."

  If I comment out the into clause, and just select, it works again.

  Has anyone had trouble with correlated sub-queries, select intos, and join type?

  selectPP.product_id

  ,PP.product_description

  ,ACC.account_number

  into##temp

  fromdbo.products as PP

  joinother_server_name.db_named.dbo.account_table as ACC on PP.productid = ACC.productid

  whereACC.period = (

  selectmax( sub.period )

  fromother_server_name.db_named.dbo.account_table as sub

  wheresub.account_number = ACC.account_number

  )

  group by PP.product_id, PP.product_description, ACC.account_number

• Kbrown

  SSCommitted

  Points: 1794

  More actions

  October 20, 2006 at 4:24 pm

  #667229

  Would something like this work for you (I didn't set up test data to check the syntax):

  select PP.product_id,PP.product_description,ACC.account_number

  into ##temp

  from dbo.products as PP

  inner join other_server_name.db_named.dbo.account_table as ACC on PP.productid = ACC.productid

  inner join (

   select sub.account_number,max( sub.period ) period

   from other_server_name.db_named.dbo.account_table as sub

   group by sub.account_number

  ) as P ACC.period = P.period

  group by PP.product_id, PP.product_description, ACC.account_number