CODEWITHSUNDEEP
sql-serverexcel
Miles Curtis
Miles Curtis

Reputation: 1

Getting invalid column name in SQL/Excel

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)

Upvotes: 0

Views: 1590

Answers (2)

Zane
Zane

Reputation: 4169

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

Upvotes: 3

Tab Alleman
Tab Alleman

Reputation: 31785

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=?))

Upvotes: 2

Related Questions