Sub query problem - 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

Transact-SQL (2005)

Sub query problem

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-22 : 18:29:35

I get the error Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "t.labtestcode" could not be bound. What am I missing can anyone tell?

With labtestcheck(labtestcode) as
(
select labtestcode from

(select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists
(Select * From lab_test_add_conf b where a.labtestcode = b.labtest_key))t
where labtestcode = t.labtestcode
)

select t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,
b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-22 : 18:36:25

There is not a table aliased as T.

quote:
Originally posted by sross81
select t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,
b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 10:49:22

I meant for the subquery called labtestcheck to be aliased as t I must be setting up wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 10:55:11

You are not even referring to the CTE in following query!

;With labtestcheck (labtestcode)
as (
	select	a.labtestcode
	from	EMR_temp_labtest_configupdate as a
	Where	Not Exists (Select * From lab_test_add_conf as b where b.labtest_key = a.labtestcode)
)

select		t.labtestcode,
		b.zsegcode,
		b.valuecode,
		b.valuedesc,
		b.valuetype,
		b.units,
		b.tablename,
		b.fieldname
from		EMR_temp_labtest_configupdate as a
inner join	emr_zseg_code_descriptions as b on a.zsegcode = b.zsegcode

E 12°55'05.25"
N 56°04'39.16"

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 11:01:06

Ok I don't see where you referred to it in the new code you posted. I tried running it and got this error:
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "t.labtestcode" could not be bound.

Also:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 't'.

Don't I need to join the CTE in the second subquery? Although first I need to alias it and for some reason I cannot get it to work....here is my updated code

With labtestcheck (labtestcode)
as (
select a.labtestcode
from EMR_temp_labtest_configupdate as a
Where Not Exists (Select * From lab_test_add_conf as b where b.labtest_key = a.labtestcode)
)t

select t.labtestcode,
b.zsegcode,
b.valuecode,
b.valuedesc,
b.valuetype,
b.units,
b.tablename,
b.fieldname
from EMR_temp_labtest_configupdate as a
inner join emr_zseg_code_descriptions as b on a.zsegcode = b.zsegcode

quote:

Originally posted by Peso

You are not even referring to the CTE in following query!

;With labtestcheck (labtestcode)
as (
	select	a.labtestcode
	from	EMR_temp_labtest_configupdate as a
	Where	Not Exists (Select * From lab_test_add_conf as b where b.labtest_key = a.labtestcode)
)

select		t.labtestcode,
		b.zsegcode,
		b.valuecode,
		b.valuedesc,
		b.valuetype,
		b.units,
		b.tablename,
		b.fieldname
from		EMR_temp_labtest_configupdate as a
inner join	emr_zseg_code_descriptions as b on a.zsegcode = b.zsegcode

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 11:08:27

Why are you aliasing the CTE? You already have given the cte it's name!

;With labtestcheck (labtestcode)
as (
	select	a.labtestcode
	from	EMR_temp_labtest_configupdate as a
	Where	Not Exists (Select * From lab_test_add_conf as b where b.labtest_key = a.labtestcode)
)

select		t.labtestcode,
		b.zsegcode,
		b.valuecode,
		b.valuedesc,
		b.valuetype,
		b.units,
		b.tablename,
		b.fieldname
from		EMR_temp_labtest_configupdate as a
inner join	emr_zseg_code_descriptions as b on a.zsegcode = b.zsegcode
inner join	labtestcheck as t on t.labtestcode = {some binding information here}

E 12°55'05.25"
N 56°04'39.16"

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 11:15:48

Thank you I was able to get that code to work. I was aliasing the CTE because that is how I had learned to do it on a different project I was helped with. I am still new to using CTE's and subquerying in general so just trying to get the technique down. Your example is great and I will be saving it in my code library for sure!

quote:

Originally posted by Peso

Why are you aliasing the CTE? You already have given the cte it's name!

;With labtestcheck (labtestcode)
as (
	select	a.labtestcode
	from	EMR_temp_labtest_configupdate as a
	Where	Not Exists (Select * From lab_test_add_conf as b where b.labtest_key = a.labtestcode)
)

select		t.labtestcode,
		b.zsegcode,
		b.valuecode,
		b.valuedesc,
		b.valuetype,
		b.units,
		b.tablename,
		b.fieldname
from		EMR_temp_labtest_configupdate as a
inner join	emr_zseg_code_descriptions as b on a.zsegcode = b.zsegcode
inner join	labtestcheck as t on t.labtestcode = {some binding information here}

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources