Hello!
Here is the statement in question:
--STATEMENT A
SELECT * FROM dbo.myTable WHERE colX in (SELECT colX FROM
dbo.sourceTable )
The problem with Statement A is that 'colX' does not exist in
'dbo.sourceTabl e'. It does, however, certainly exist in 'dbo.myTable'.
Breaking the statement down, we have:
--STATEMENT B
SELECT colX FROM dbo.sourceTable
...which returns the following error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'colX'.
--STATEMENT C
SELECT colX FROM dbo.myTable
....which returns results,
If we modify Statement A to use a join:
--STATEMENT D
SELECT myTable.*
FROM dbo.myTable
JOIN dbo.sourceTable ON sourceTable.col X=myTable.colX
....we get the error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'colX'.
Any idea what SQL Server is doing in Statement A?!?!
Thanks!
Anne 5 8034
On Wed, 24 Oct 2007 14:42:23 -0000, Anne <ni*********@gm ail.com>
wrote:
>Here is the statement in question: --STATEMENT A
SELECT * FROM dbo.myTable WHERE colX in (SELECT colX FROM dbo.sourceTabl e)
The problem with Statement A is that 'colX' does not exist in 'dbo.sourceTab le'. It does, however, certainly exist in 'dbo.myTable'. Breaking the statement down, we have:
>Any idea what SQL Server is doing in Statement A?!?!
In the subquery you can refer to any column in dbo.myTable or
dbo.sourceTable , they are both "available" . Since the reference to
colx does not have a qualifier, and there is no colX in
dbo.sourceTable , the one from dbo.myTable is used. Had the column
existed in both tables, SQL Server would, by design, use the one from
table defined in the subquery for any un-qualified reference.
The best way to prevent this sort of thing is to always qualify the
column references in the subquery.
Roy Harvey
Beacon Falls, CT
Thanks!
But why/how is colX "available" in both tables? Maybe there are some
basics that I don't understand...
On Wed, 24 Oct 2007 17:54:28 -0000, Anne <ni*********@gm ail.com>
wrote:
>Thanks!
But why/how is colX "available" in both tables? Maybe there are some basics that I don't understand...
By available I meant that there is a column with that name in the
table.
Pretty much everywhere else in SQL if there are two tables with the
same column name, and you do not qualify a reference to the column
name, an error is returned that there is an ambiguous reference. The
one exception that I know about is with a subquery. INSIDE the
subquery the rules are a little different. First it tries to resolve
the un-qualified reference using the table(s) in the FROM clause of
the subquery. If there is only one table and it has that column, that
is the particular column of that name the subquery uses. If there are
multiple tables in the subquery FROM clause and only one of them has a
column of that name, that is the particular column of that name the
subquery uses. However, if NONE of the tables in the subquery's FROM
clause has that a column with that name, THEN it will look to resolve
the unqualified column name against the tables in the outer query's
FROM clause. If there is only one such reference it uses it, if there
is more than one it will error as an ambiguous reference.
I don't know if that is any clearer, but it is more complete.
Roy Harvey
Beacon Falls, CT
Yes! That is exactly what I was looking for. I needed to know what
steps SQL Server was taking to get to the point where the query ran in
the manner that it did. Thank you SO much!!!
>But why/how is colX "available" in both tables? <<
colx is available to **the subquery** and not in both table at all.
Queries first try to reference the nearest column, and move outward in
the scope of query nesting.
>Maybe there are some basics that I don't understand... <<
Scoping rules in SQL are similar to the scoping rules in block
structure programming languages like Algol, C, PL/I, ADA, etc.
A: BEGIN
x INTEGER;
y INTEGER;
..
B: BEGIN
x INTEGER;
x := x + y;
..
END;
END;
When you get to block B, the local x is referenced in that assignment
statement, but the y in the containing block A is referenced.
But SQL is a bit more complicated. If two query expression are on the
same level, then you have to use a LATERAL operator in Standard SQL.
When a query expression is given a name and is it called a derived
table. Only that derived table is exposed to higher nesting level.
I have a chapter in THINKING IN SETS on this; the book is due out in
February 2008. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Shino |
last post by:
Hi,
Can anyone help with this error: "ORA-00904: invalid column name"?
Thanks!
SQL> create view PPFa as
2 SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID
AS StudID
3 FROM User_Lecturer L, User_Student S, TBL_Intake I, Users U
4 WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
|
by: rockie12 |
last post by:
I have a db that has a table x in it called data1
I have a program that does to things, updates values in the data1
table and also inserts new rows into this table. The update existing
values works great. Then when the insert loop runs, I get this error
on the following line.
insert into data1 ('AdminManageLogIn','Password') Values ('123','222')
Server: Msg 208, Level 16, State 1, Line 1
|
by: Blake Caraway |
last post by:
All,
I've seen several posts regarding using UNION or UNION ALL to mash
together two or more resultsets into a single result set, but can't
seem to find enough info here to help me answer my particular
question.
I have a stored procedure that gets the column names in a particular
format (i.e. "chassis_id"|"chassis_description"|"modify_date") as well
as actual data for a given table (in a quote-separated, pipe-delimited
|
by: olanorm |
last post by:
I have a query where one or more of the columns returned is a result
from a subquery. These columns get their own alias. I want to filter
out the rows containing NULL from the subqueries but it just won't
work. When running columnAlias IS NOT NULL i just get the error
"Invalid column name 'columnAlias'.
This is the query:
SELECT k.UserId, k.Lastname, k.Firstname,
(SELECT kscr.answer FROM Results kscr WHERE kscr.UserID =
|
by: Greg Stark |
last post by:
Postgresql 7.4b2 (approximately, compiled out of CVS)
When I have a subquery that has a complex subquery as one of the result
columns, and then that result column is used multiple times in the parent
query, the subquery is inlined for each one. This means multiple redundant
executions of the subquery.
I recall there was a way to defeat this optimization involving introducing an
extra subquery layer somewhere. But I'm failing to be able...
| |
by: Raquel |
last post by:
This simple piece of code in a try-catch block in SQLJ stored
procedure is failing with CLI0611E - invalid column name:
#sql sproc3_iterator = {SELECT FIRSTNME, LASTNAME FROM
DB2ADMIN.EMPLOYEE WHERE WORKDEPT = :workdept};
rs1 = sproc3_iterator.getResultSet();
ctx.close();
I have (re)checked that FIRSTNME nad LATNAME are valid columns of the
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values...
|
by: theGeek |
last post by:
I always wonder which one of join or subquery should I be using to solve a particuar problem so that I get the correct result set in minimum time. It usually happens that I can write a query quickly using subqueries (I think I happen to think that way more easily than join way :)) but I see others making use of joins all the time. May be that they can think of a query more easily using joins. I am saying that because I don't usually see much of...
|
by: cellus205 |
last post by:
Hows it going everyone. Im having a little trouble with a memo field and a subquery in my Win32 App, Im using an Access DB. The query was working fine before, but in my access db, I had to change the Filename column from Text to Memo because the text type was limited to 255 characters. Now I am getting an error "Invalid Memo, OLE, or Hyperlink Object in 'Filename'." Im using this query to fill a datatable via TableAdapter.
Heres the query:
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |