Table reference in subqueries.

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 2000 Forums

SQL Server Development (2000)

Table reference in subqueries.

Author

Topic

infobox
Starting Member

3 Posts

Posted - 2005-12-22 : 09:56:34

Friends,

Following is the correct query and its working fine.

QUERY:

select fld_script_code,(select fld_script_code
from (select fld_script_code
from tbl_script_master
where fld_script_id = sm.fld_script_id) as t)
from tbl_script_master as sm
where fld_script_id = 2

The OUTPUT of this query is :

----------------------------------------------------
fld_script_code | (No column name)
----------------------------------------------------
ABC | ABC
----------------------------------------------------

Now, if I add one more subquery like follows:

select fld_script_code,(select fld_script_code
from (select fld_script_code
from (select fld_script_code
from tbl_script_master
where fld_script_id = sm.fld_script_id) as t)as t1)
from tbl_script_master as sm
where fld_script_id = 2

It gives follwoing ERROR:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'sm' does not match with a table name or alias name
used in the query.

Can anybody explain why this happens? Is there any subquery limit in
SQL Server?

Thanks,
-Chintan.

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-22 : 10:59:20

You're missing a label....


SELECT    fld_script_code
	,( SELECT fld_script_code 
	     FROM (SELECT fld_script_code 
		     FROM (SELECT fld_script_code 
			     FROM tbl_script_master 
			    WHERE fld_script_id = sm.fld_script_id
			   ) as t
		   )as t1 
	  ) AS Missing_Label
  FROM tbl_script_master as sm 
 WHERE fld_script_id = 2

But unless this is part of a more complicated query, this is pretty useless. There's no need for this

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-12-22 : 15:09:27

Homework?

infobox
Starting Member

3 Posts

Posted - 2005-12-22 : 23:59:03

Dear X002548,

It will not make any difference. I have checked it but still it gives the same error.

Thanks,
-Chintan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources