CodingForums.com - Ambiguous column name 'voltage'

CodingForums.com (http://www.codingforums.com/index.php)

- ASP (http://www.codingforums.com/forumdisplay.php?f=8)

- - Ambiguous column name 'voltage' (http://www.codingforums.com/showthread.php?t=267649)

dla314

07-11-2012 10:09 PM

Ambiguous column name 'voltage'

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 (Post 1250664)

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 (Post 1250678)

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 (Post 1250686)

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 :eek:

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.

All times are GMT +1. The time now is 05:45 PM.