[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 982
  • Last Modified:

Ambiguous Column Name; Non-JOIN Query; Only When ORDER BY Clause is Added

I have read most of the posts on this error but I am not getting it.

This 'Ambiguous column name' error does not happen unitl I introduce the ORDER BY clause and the field.

There's no JOIN of any kind.  

There's only the one table in the Query.

SELECT
	TOP 100
	sField001 ,
	sField002 ,
	sField003 ,
	nField004 ,
	nField005 ,
	nField006 ,
	* -- <<<<< notice this inclusion
	FROM
		dbo.tblData -- <<< notice only one table is specified
	WHERE
			sField002 > '20081200000000.000'
		AND	sField003 IS NOT NULL
		AND	sField001 = 'tf'
		AND	nField005 > 1
	ORDER BY
		nField004 ASC  -- <<< ambigous error points here

Open in new window

0
MadraBeag
Asked:
MadraBeag
1 Solution
 
chapmandewCommented:
It is because of the *...it doesn't know which field you want to order by...even though it is the exact same field in the SELECT list.  In your scenario, you could change this:

order by nField004

to

order by 4

using this method allows you to order by an ordinal field in the select statement.
0
 
chapmandewCommented:
of course, the easiest thing to do is just take out the * from the select list.
0
 
tpi007Commented:
The column nField004 appears in result set twice - once via explicit column and once via * caluse in select. The query engine cannot distinguiish between the 2 identical column names for order  by.
0
 
Kevin CrossChief Technology OfficerCommented:
Agree with Tim.  Why mix use of * there and explicit column names?  If you are needing to create new columns with data from existing columns then just give an alias.
SELECT
	TOP 100
	sField001 as s01,
	sField002 as s02,
	sField003 as s03,
	nField004 as n04,
	nField005 as n05,
	nField006 as n06,
	* -- <<<<< notice this inclusion
	FROM
		dbo.tblData -- <<< notice only one table is specified
	WHERE
			sField002 > '20081200000000.000'
		AND	sField003 IS NOT NULL
		AND	sField001 = 'tf'
		AND	nField005 > 1
	ORDER BY
		n04 ASC  -- <<< ambigous error points here

Open in new window

0
 
MadraBeagAuthor Commented:
I do not recall this happening in MS SQL 2000.   I'll test this.  Seems sort of lousy.  In my humble opinion, if one specifies columns to display first and one is in the ORDER BY clause, that explicit listed column is sorted not the implicit column found in the * specifier.  But that's just me.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now