Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,836
    Thanks
    42
    Thanked 199 Times in 198 Posts
    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

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #4
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #5
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,836
    Thanks
    42
    Thanked 199 Times in 198 Posts
    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

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #6
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    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.
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #7
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,836
    Thanks
    42
    Thanked 199 Times in 198 Posts
    Quote Originally Posted by WolfShade View Post
    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.

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #8
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by alykins View Post
    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.

  • #9
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,836
    Thanks
    42
    Thanked 199 Times in 198 Posts
    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)?

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by alykins View Post
    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?
    Last edited by dla314; 07-12-2012 at 08:53 PM.

  • #12
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,836
    Thanks
    42
    Thanked 199 Times in 198 Posts
    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

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #14
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    voltage should be P2.voltage, I _think_.
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •