CODEWITHSUNDEEP
sqlsql-server
Angelo Badellino
Angelo Badellino

Reputation: 2201

Strange SELECT error code

Ho threre. When I run a simple select on some tables like:

SELECT
    ValidoDa, 
    Tab_Ticket_Default,
    * 
FROM 
    Dipendente_Stor
WHERE 
    CodiceAbi = '08753' and 
    Matricola = 98
order by 
     ValidoDa

I can get the error "Ambiguous column name 'ValidoDa'"

What is the reason? There is only one filed called ValidoDa!

can you explain this behaviour?

Upvotes: 2

Views: 85

Answers (4)

gbn
gbn

Reputation: 432421

ORDER BY will first attempt to resolves SELECT column list.

In this case, you have ValidoDa twice therefore it is ambiguous

You can fix it like this with an alias, but SELECT * is bad practice anyway

SELECT
    ValidoDa AS ValidoDaFOO, 
    Tab_Ticket_Default,
    * 
FROM 
    Dipendente_Stor
WHERE 
    CodiceAbi = '08753' and 
    Matricola = 98
order by 
     ValidoDa 
     -- or ValidoDaFOO

Upvotes: 3

vulkanino
vulkanino

Reputation: 9134

I think that saying

order by ValidoDa

SQL doesn't know which field to use: the explicit ValidoDa or the *.ValidoDa. If you really need the * (I would remove it) then call the first ValidoDa with an alias:

SELECT
    ValidoDa AS DaQuando, 
    Tab_Ticket_Default,
    * 
FROM 
    Dipendente_Stor
WHERE 
    CodiceAbi = '08753' and 
    Matricola = 98
order by 
     DaQuando

Upvotes: 2

StuartLC
StuartLC

Reputation: 107317

Because you are selecting the field ValidoDa twice - once explicitly, and then again with the *.

Try

SELECT     ValidoDa,      
  Tab_Ticket_Default,     
  *  
FROM      
  Dipendente_Stor 
WHERE      
  CodiceAbi = '08753' 
  and      
  Matricola = 98 
order by       
   1

Or 

SELECT     ValidoDa,      
  Tab_Ticket_Default,     
   ** all the other fields of Dipendente_Stor **
FROM      
  Dipendente_Stor 
WHERE      
  CodiceAbi = '08753' 
  and      
  Matricola = 98 
order by       
   ValidoDa

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

There is one column with this name but you show it twice in the SELECT list!

This is not a problem per se, SQL allows that (although I think this is a major design flaw of the language).

The problem resurfaces when you try to use that column in the ORDER BY clause. Now (that there are two columns with same name) SQL complains that it can't know which one you mean.

Upvotes: 4

Related Questions