common table expression error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

common table expression error

Author

Topic

senapathy
Starting Member

1 Post

Posted - 2011-12-07 : 08:40:42

Hello,

using
WITH

doc_reference as

(select dbo.job_attr.attr_value from dbo.job_attr where dbo.job.wo_id = dbo.job_attr.wo_id and dbo.job.oper_id = dbo.job_attr.oper_id and dbo.job.seq_no = dbo.job_attr.seq_no
and dbo.job_attr.attr_id = (select attr_id from dbo.attr where attr_desc = 'Reference')) ,

substrate as

(select dbo.job_attr.attr_value from dbo.job_attr where dbo.job.wo_id = dbo.job_attr.wo_id and dbo.job.oper_id = dbo.job_attr.oper_id and dbo.job.seq_no = dbo.job_attr.seq_no
and dbo.job_attr.attr_id = (select attr_id from dbo.attr where attr_desc = 'Substrate'))
--job.spare3 as available)

select attr_value from
doc_reference,
substrate

i got error message
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.job.wo_id" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.job.oper_id" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.job.seq_no" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.job.wo_id" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.job.oper_id" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.job.seq_no" could not be bound.

please help me for this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 08:46:04

you've not used the table dbo.job in query at all and you're referring lot of columns from it in your where clause. i think you need to add a join to that table


...
select dbo.job_attr.attr_value from dbo.job_attr where dbo.job.wo_id = dbo.job_attr.wo_id and dbo.job..oper_id = dbo.job_attr.oper_id and dbo.job..seq_no = dbo.job_attr.seq_no 
...

and dbo.job_attr.attr_id = (select attr_id from dbo.attr where attr_desc = 'Reference'))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 08:48:48

it should be modified as below


WITH
doc_reference as

(select dbo.job_attr.attr_value 
from dbo.job_attr 
join dbo.job
on dbo.job.wo_id = dbo.job_attr.wo_id 
and dbo.job.oper_id = dbo.job_attr.oper_id 
and dbo.job.seq_no = dbo.job_attr.seq_no 
where dbo.job_attr.attr_id = (select attr_id from dbo.attr where attr_desc = 'Reference')) ,
substrate as
(select dbo.job_attr.attr_value 
from dbo.job_attr 
join dbo.job
on dbo.job.wo_id = dbo.job_attr.wo_id 
and dbo.job.oper_id = dbo.job_attr.oper_id 
and dbo.job.seq_no = dbo.job_attr.seq_no 
where dbo.job_attr.attr_id = (select attr_id from dbo.attr where attr_desc = 'Substrate'))
--job.spare3 as available)

select attr_value from 
doc_reference,
substrate
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources