Getting invalid column name in SQL/Excel

Question

I will preface this by saying I am nowhere near being an expert in SQL. Using Excel I am trying to use one specific cell as the input to query, but I run into a problem where a column I created isn't defined as a column. Please help.

 SELECT
CASE  
WHEN CHARINDEX(',', TCPIPADDRESS) > 0 THEN LEFT(TCPIPADDRESS, CHARINDEX(',', TCPIPADDRESS) - 1) ELSE TCPIPADDRESS END AS IPADDRESS,   
ADMachine.ADMachineName, ADMachine.SerialNumber, 
ADMachine.OperatingSystem, ADUsers.ADUser, ADUsers.ADDisplayName, employee_data.employee_first_name, 
employee_data.employee_last_name, asset_center.LOCATION_SITENAME, asset_center.TCPIPHOSTNAME, 
asset_center.MAC_ADDRESS, ADUsers.ADUserOU
FROM PC_GAP.dbo.ADMachine ADMachine, PC_GAP.dbo.ADUsers ADUsers, PC_GAP.dbo.asset_center asset_center, PC_GAP.dbo.employee_data employee_data
WHERE ADUsers.ADUser = employee_data.employee_user_name AND ADMachine.SerialNumber = asset_center.SERIALNO AND ADUsers.ADUser = asset_center.LAST_LOGGED_ON_USER

The IPAddress at the end is where the problem lies.

Edit 1: Added the additional information from the SQL statement to paint the whole picture (originally left out irrelevant data)

What problem are you running into? What is the error you are getting? A little bit more of the specifics would help — Pseudonym
– Pseudonym, Commented Mar 27, 2015 at 16:21
I have this code entered in excel/microsoft query. When I add the last bit [AND ((IPAddress=?))] to allow me to set the parameters for a cell to act as the "?" I get an error that says invalid column name. — Miles Curtis
– Miles Curtis, Commented Mar 27, 2015 at 16:24

Community · Accepted Answer · 2020-07-29 18:25:24Z

3

Alright so first off you need to stop using the old style joins for all of the reasons listed here.

You are also going to run into an issue that you aren't joining on anything other than one of your tables. I'm pretty sure this will not give you the results that you are looking for.

Then finally you need to think about the order of operations. Since the WHERE clause is evaluated before the the select you cannot refer to your alias in the where clause. You can only reference an alias with an ORDER BY or by using a subquery or a cte.

You can however use your case expression in your where clause. The example would be as follows.

CASE
  WHEN CHARINDEX(',', TCPIPADDRESS) > 0 
  THEN LEFT(TCPIPADDRESS, CHARINDEX(',', TCPIPADDRESS) - 1) 
  ELSE TCPIPADDRESS 
END = ?

As stated previously you could also turn this entire thing into a subquery. I would use your code as an example for that but I'm not entirely sure what exactly you're hoping to accomplish with your current set of joins so my example will be a bit generic.

select
    GenericColumn
    (select
        blah as Pity,
        GenericColumn
    from dbo.TheFoo)
where Pity = SeachCondition

edited Jul 29, 2020 at 18:25

CommunityBot

11 silver badge

answered Mar 27, 2015 at 16:30

Zane

4,1691 gold badge23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Miles Curtis Over a year ago

We were basically trying to make it where you could enter an IP address and see who owned that machine and some of the information behind it. Unfortunately we do not own any of the data and the groups that do have taken horrible care of it. The IP address field sometimes has just an IPv4 address and sometimes has both IPv4 and IPv6 addresses separated by a comma. We wanted to search just on the IPv4.

Zane Over a year ago

Yes but the way you're defining your joins are going to give you a cartesian product.

Andriy M Over a year ago

You could also throw in some sugar to replace the CASE expression with a possibly shorter one: ISNULL(LEFT(TCPIPADDRESS, NULLIF(CHARINDEX(',', TCPIPADDRESS), 0) - 1), TCPIPADDRESS).

Tab Alleman · Accepted Answer · 2015-03-27 16:28:04Z

2

You can't use a column alias in the WHERE clause. You have to use the whole formula that you aliased.

WHERE ADMachine.SerialNumber = asset_center.SERIALNO AND ((CASE  
WHEN CHARINDEX(',', TCPIPADDRESS) > 0 THEN LEFT(TCPIPADDRESS, CHARINDEX(',', TCPIPADDRESS) - 1) ELSE TCPIPADDRESS END=?))

To illustrate Aaron's suggestion, it would look more like this:

WITH cte AS (
SELECT    
CASE  
  WHEN CHARINDEX(',', TCPIPADDRESS) > 0 THEN LEFT(TCPIPADDRESS, CHARINDEX(',', TCPIPADDRESS) - 1) ELSE TCPIPADDRESS END AS IPADDRESS,   
ADMachine.ADMachineName,  
ADMachine.OperatingSystem,
asset_center.MAC_ADDRESS,

FROM PC_GAP.dbo.ADMachine ADMachine, PC_GAP.dbo.ADUsers ADUsers, PC_GAP.dbo.asset_center asset_center, PC_GAP.dbo.employee_data employee_data
)
  SELECT * FROM cte
WHERE ADMachine.SerialNumber = asset_center.SERIALNO AND ((IPAddress=?))

edited Mar 27, 2015 at 16:28

answered Mar 27, 2015 at 16:17

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

6 Comments

Miles Curtis Over a year ago

will this allow me to use a cell as the sort/filter criteria though?

Aaron Bertrand Over a year ago

Or an easier way would be to derive the expression in a CTE, subquery, derived table, etc. Then the outer query can use the alias in any clause.

Tab Alleman Over a year ago

@MilesCurtis well that I don't know because I'm not an Excel expert. I just know that this will fix your SQL syntax problem. You could also try Aaron's suggestion to see if either or both of them work for your Excel purposes.

Miles Curtis Over a year ago

I will give that a shot and report back!

Miles Curtis Over a year ago

When I try this the entire where part lists as not being able to be bound

|

Collectives™ on Stack Overflow

Getting invalid column name in SQL/Excel

2 Answers 2

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related