Ambiguous column name 'voltage'

dla314 · 07-11-2012, 10:09 PM

I'm fairly new to SQL and I ran into an Ambiguous column name error. Here's the error I received on my AJAX request:

Code:

<font face="Arial" size=2>
<p>Microsoft OLE DB Provider for SQL Server</font> <font face="Arial" size=2>error '80040e14'</font>
<p>
<font face="Arial" size=2>Ambiguous column name 'voltage'.</font>
<p>
<font face="Arial" size=2>/common/ver7/usmanajax_search_ver7.asp</font><font face="Arial" size=2>, line 135</font>

I was trying to run the following query:

Code:

whereStrSql=" left join excludedproducts E on (E.storeid=" & Session("StoreID") & " and E.id=P2.id) " 
        whereStrSql=whereStrSql & " where " & strsql


	strsql="select P2.voltage,V.voltage voltage2,count(*) as KOL"
	strsql=strsql & " from Lights_America..temp_products P2 "
	strsql=strsql & " left join  voltage V on (P2.voltage=V.id)"
	strsql=strsql & whereStrSql
	strSql=strSql & " group by P2.voltage,V.voltage"
	strSql=strSql & " order by voltage2"

Any help would be greatly appreciated. Thanks in advance.

Old Pedant · 07-11-2012, 11:18 PM

Show us what the FULL SQL query/queries looks like.

Put in this line just AFTER your "...order by voltag2" line:

Code:

Response.Write "<hr>whereStrSql: " & whereStrSql & "<hr>strSql: " & strSql & "<hr>" & vbNewLine

alykins · 07-12-2012, 12:40 PM

From what was provided the query should look like this

Code:

SELECT P2.voltage,V.voltage voltage2,count(*) AS KOL 
FROM Lights_America..temp_products P2 
LEFT JOIN  voltage V ON (P2.voltage=V.id) 
LEFT JOIN excludedproducts E ON (E.storeid=SessionID AND E.id=P2.id) 
WHERE ??????????????????????  
GROUP BY P2.voltage,V.voltage 
ORDER BY voltage2

Looking at that I see on problem of two .. in this line

Code:

FROM Lights_America..temp_products P2

and then you also have a table named (supposedly) "voltage" and you are selecting a column named "voltage" ... Are you sure your table is named that? If so try putting [] around the column so these lines would change to

Code:

LEFT JOIN  voltage V ON (P2.[voltage]=V.id)
GROUP BY P2.[voltage],V.[voltage]

but since your join is joining two totally different column names I am guessing you have miss-named the column... your foreign key should have the same name so it should look like

Code:

(P2.id=V.id)
-- or since you should call out the id's a little more detailed
P2.volt_id = V.volt_id

Doing as OldPedant will help though as well since we have no idea what strsql is until after whereStrSql is created

dla314 · 07-12-2012, 04:50 PM

Here's the full SQL sorry about that:

Code:

select P2.voltage,V.voltage voltage2,count(*) as KOL from Lights_America..temp_products P2  left join  voltage V on (P2.voltage=V.id) left join excludedproducts E on (E.storeid=9 and E.id=P2.id)  where  P2.id>0  and voltage in (1)  and manufacturer_name in (143,135,153,171,151,217,184,185,181,179,183,175,150,123,130,129,121,128,18,14,66,37,77,65,11,68,46,60,43,41,45,108,38,98,40,26,48,25,28,42,62,13,9,30,39,70,47,64,74,116,33,97,12,107,101,16,29,57,59,7,15,71,1,72,54,10,19,51,53,35,69,114,118,17,67,52,63,34,102,76,31,191,188,190,133,134,194,196,160,159,163,209,216,213,214,211,139,58,208,204,168,166,142,165,164,138,140)  and isnull(E.exc,0)<1  group by P2.voltage,V.voltage order by voltage2

alykins · 07-12-2012, 06:20 PM

Code:

select P2.voltage,V.voltage voltage2,count(*) as KOL -- missing a comma between V.voltage, voltage2 and an alias identifier ie P2.voltage2 or V.voltage2
from Lights_America..temp_products P2  -- two .'s in this line Lights_America.temp_products
left join  voltage V on (P2.voltage=V.id) 
left join excludedproducts E on (E.storeid=9 and E.id=P2.id)  
where  P2.id > 0  
	and voltage in (1)  
	and manufacturer_name in (143,135,153,171,151,217,184,185,181,179,183,175,150,123,130,129,121,
								128,18,14,66,37,77,65,11,68,46,60,43,41,45,108,38,98,40,26,48,25,28,
								42,62,13,9,30,39,70,47,64,74,116,33,97,12,107,101,16,29,57,59,7,15,71,1,72,
								54,10,19,51,53,35,69,114,118,17,67,52,63,34,102,76,31,191,188,190,133,134,
								194,196,160,159,163,209,216,213,214,211,139,58,208,204,168,166,142,165,164,138,140)  
	and isnull(E.exc,0)<1 
group by P2.voltage,V.voltage 
order by voltage2

WolfShade · 07-12-2012, 07:27 PM

If it's MS-SQL, it should be V.voltage as voltage2

If it's Oracle, it is correct (no "as"). Not sure about MySQL.

alykins · 07-12-2012, 08:05 PM

Quote:

Originally Posted by WolfShade

If it's MS-SQL, it should be V.voltage as voltage2

If it's Oracle, it is correct (no "as"). Not sure about MySQL.

I got the impression that it was TSQL- but you raise a good point...

@OP: What DB are you using? SQL2008R2? SQLEXPRESS? MySQL?
What management tool are you using? I think you need to take your query and get it working in your management tool. You design the queries first, then incorporate them into code... not find and error and then try to figure out if it is ASP or SQL or 'other' error; guarantee your SQL then worry about debugging ASP code.

dla314 · 07-12-2012, 08:08 PM

Quote:

Originally Posted by alykins

I got the impression that it was TSQL- but you raise a good point...

@OP: What DB are you using? SQL2008R2? SQLEXPRESS? MySQL?
What management tool are you using? I think you need to take your query and get it working in your management tool. You design the queries first, then incorporate them into code... not find and error and then try to figure out if it is ASP or SQL or 'other' error; guarantee your SQL then worry about debugging ASP code.

I'm currently using Microsoft SQL Server 2005 if that helps.This issue has apparently existed for years and is a very old system. So we're just debugging code that was outsourced to Russia a long time ago and the company we outsourced it to no longer exists. So we're kind of stuck with it.

alykins · 07-12-2012, 08:14 PM

ok- so are you using Microsoft SQL Server Management Studio? Copy the query you posted to a new query and try it- does it work? Why does it fail (if it does)?

Old Pedant · 07-12-2012, 08:42 PM

First of all, SQL Server accepts *EITHER*

Code:

    V.voltage AS voltage2
or 
    V.voltage voltage2

Personally, I *always* use AS just to make the code crystal clear.

And the "voltage" that is ambiguous is the one in red below:

Code:

select P2.voltage,V.voltage AS voltage2,count(*) as KOL 
from Lights_America..temp_products AS P2 
left join  voltage AS V on (P2.voltage=V.id) 
left join excludedproducts AS E on (E.storeid=9 and E.id=P2.id)  
WHERE  P2.id > 0  
AND voltage in (1)  
AND manufacturer_name in (143,135,...,140)  
AND isnull(E.exc,0) < 1 
group by P2.voltage,V.voltage 
order by voltage2

BUT...this query has other problems.

AND isnull(E.exc,0) < 1 will effectively convert the LEFT JOIN on E into an INNER JOIN.

See here:
http://www.codingforums.com/showthre...192#post818192

Possibly true of AND manufacturer_name in (143,135,...,140) as well. Can't tell, since the query doesn't say what table that field is coming from.

Oh...and the two period in Lights_America..temp_products *ARE* legal. Normally, in SQL Server, you'd expect to see Lights_America.dbo.temp_products but I believe that when you omit the "owner" [that's what dbo is] (that is, have two periods) it means accept any owner.

dla314 · 07-12-2012, 08:49 PM

Quote:

Originally Posted by alykins

ok- so are you using Microsoft SQL Server Management Studio? Copy the query you posted to a new query and try it- does it work? Why does it fail (if it does)?

It produced the following error when I ran the query in Microsoft SQL Server Management Studio:

Code:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'voltage'.

I also cleaned up the query a bit to make it a bit more readable:

Code:

select P2.voltage,V.voltage voltage2,count(*) as KOL 
from Lights_America..temp_products P2  
left join  voltage V on (P2.voltage=V.id) 
left join excludedproducts E on (E.storeid=9 and E.id=P2.id)  
where  P2.id>0  
and voltage in (1)  
and manufacturer_name in (143,135,153,171,151,217,184,185,181,179,183,175,150,123,130,129,121,128,18,14,66,37,77,65,11,68,46,60,43,41,45,108,38,98,40,26,48,25,28,42,62,13,9,30,39,70,47,64,74,116,33,97,12,107,101,16,29,57,59,7,15,71,1,72,54,10,19,51,53,35,69,114,118,17,67,52,63,34,102,76,31,191,188,190,133,134,194,196,160,159,163,209,216,213,214,211,139,58,208,204,168,166,142,165,164,138,140)  
and isnull(E.exc,0)<1  
group by P2.voltage,V.voltage 
order by voltage2

Does that help?

alykins · 07-12-2012, 08:51 PM

ah- did not know you could omit the "as" or the between ..'s ... I still though stand by my statement that should be tested in SQL query- it would have said which line the problem was instead of guessing games

Old Pedant · 07-13-2012, 07:04 PM

dla: I *SHOWED* you the error. Look at the word voltage in red in my last post. SQL Server does NOT KNOW which "voltage" you mean by that one. Put the table alias in front of it.

But ALSO read what I wrote about LEFT JOIN being converted to INNER JOIN by your other mistake.

WolfShade · 07-13-2012, 07:11 PM

voltage should be P2.voltage, I _think_.

Old Pedant · 07-13-2012, 07:17 PM

I *THINK* that this fixes both problems. But of course I can't be sure:

Code:

select P2.voltage,V.voltage AS voltage2,count(*) as KOL 
from Lights_America..temp_products AS P2 
left join  voltage AS V on (P2.voltage=V.id) 
left join excludedproducts AS E on (E.storeid=9 and E.id=P2.id AND isnull(E.exc,0) < 1 )  
WHERE  P2.id > 0  
AND P2.voltage in (1)  
AND P2.manufacturer_name in (143,135,...,140)  
group by P2.voltage,V.voltage 
order by voltage2

Also, if manufacurer_name is *NOT* in "P2" then the query still has problems, so I put it there.