The multi-part identifier "##ABMPARENT.SYM_INDEX" could not be bound

Question

I'm running the code below in SQL server 2012 and am getting an error msg saying that

"The multi-part identifier "##ABMPARENT.SYM_INDEX" could not be bound."

I've looked through several over related threads which usually result in someone having spelt the table name incorrectly, used the where clause more than once in a single select statement etc ... However I've used the intellisense and have referenced the correct column / table as far as I can see.

Can anybody tell what is causing the error?

Thanks

Ps. I don't have access to save a SQL view which is why I'm using temp tables for this.

IF OBJECT_ID('tempdb..#ABMPARENT') IS NOT NULL
  DROP TABLE ##ABMPARENT

IF OBJECT_ID('tempdb..#ALLSYMBOLS') IS NOT NULL
  DROP TABLE ##ALLSYMBOLS

SELECT dbo.KLX_PARENT_CHILD.PARENT_NAME ,
       dbo.KLX_MASTER_SYMBOL.SYM_INDEX  ,
       dbo.KLX_MASTER_SYMBOL.SYM_NAME   ,
       dbo.KLX_SYM_DESC.DESCRIPTION
INTO ##ABMPARENT
FROM       dbo.KLX_PARENT_CHILD
INNER JOIN dbo.KLX_MASTER_SYMBOL ON dbo.KLX_PARENT_CHILD.SYM_INDEX = dbo.KLX_MASTER_SYMBOL.SYM_INDEX
INNER JOIN dbo.KLX_SYM_DESC ON dbo.KLX_MASTER_SYMBOL.SYM_INDEX = dbo.KLX_SYM_DESC.MICRO_OBJ_ID
WHERE ( dbo.KLX_PARENT_CHILD.PARENT_NAME = 'ABMRCTREE_1314' )
  AND ( dbo.KLX_MASTER_SYMBOL.SYM_NAME LIKE 'LVRC%' )

SELECT dbo.KLX_PARENT_CHILD.PARENT_NAME  ,
       dbo.KLX_MASTER_SYMBOL.SYM_INDEX   ,
       dbo.KLX_MASTER_SYMBOL.SYM_NAME    ,
       dbo.KLX_SYM_DESC.DESCRIPTION      ,
       dbo.KLX_PARENT_CHILD.PARENT_INDEX
INTO ##ALLSYMBOLS
FROM       dbo.KLX_PARENT_CHILD
INNER JOIN dbo.KLX_MASTER_SYMBOL ON dbo.KLX_PARENT_CHILD.SYM_INDEX = dbo.KLX_MASTER_SYMBOL.SYM_INDEX
INNER JOIN dbo.KLX_SYM_DESC      ON dbo.KLX_MASTER_SYMBOL.SYM_INDEX = dbo.KLX_SYM_DESC.MICRO_OBJ_ID

SELECT *
FROM ##ALLSYMBOLS
where [##ABMPARENT].SYM_INDEX = ##ALLSYMBOLS.PARENT_INDEX

Answer 1

Your statement SELECT * FROM ##ALLSYMBOLS where [##ABMPARENT].SYM_INDEX = ##ALLSYMBOLS.PARENT_INDEX does not reference a table named [##ABMPARENT], so you cannot reference a column of that table. Did you intend to do a join in that query?

Answer 2

What @Gabe said. Your last select statment is referencing a table that is not in the from clause.

Nothing wrong with temp tables, but why are you using global temp tables (##some-table-name) instead of local temp tables (#some-table-name). Global temp tables are visible to every session, and are, in my experience, a code smell.

Local temp tables are scoped to to your session (or if created within a stored procedure, to a single stored procedure exection. Local temp tables are dropped when the stored procedure that created exits or when the session is closed, whichever comes first. Further, local temp table tables, once created in a stored procedure are visible to nested stored procedures -- e.g., stored procedure A creates temp table #WORK and then invokes stored procedure B which in turn invokes stored procedure C. The temp table #WORK is usable and visible to both stored procedures B and C.

It complicates stored procedure creation though. A stored procedure referencing a temp table that it doesn't create can't be created unless that temp table exists for the connection at the time create procedure is executed, so you need to do something like:

create table #foo ( ... )
go
create proc dbo.A as ...
go
drop table #foo
go

And a code suggestion: use correlation names for your tables, like so:

select *
from dbo.Foo t1
join dbo.Bar t2 on t2.id = t1.bar_id

Your SQL will be way more readable and, you only have to type the long convoluted table name once. You, your coworkers and the physical therapist you've been seeing for repetitive stress syndrome will appreciate it.