SQL Help?

I need to compare the Nodes column SysName with Nodes column DNSname to determine any nodes which have DNS entries (hostnames) that do not match their SysName. I've created this SQL query to compare the two Nodes columns using the SUBSTRING function to strip only the hostname part from the stored FQDN string. The SysName and DNS columns are stored as FQDN strings. The SQL query executes okay as coded below with given WHERE clause (not the result I'm looking for) but it will throw 'Invalid column name 'SysName_hostname' error when using Alias name with the WHERE clause (WHERE SysName_hostname <> DNS_hostname). I understand why this is. Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. I've tried using the SUBSTRING functions for the conditional in the WHERE clause (WHERE SUBSTRING(Nodes.SysName, 1, CHARINDEX('.', Nodes.SysName) - 1) <> SUBSTRING(Nodes.DNS, 1, CHARINDEX('.', Nodes.DNS) - 1)

but this throws the error 'Invalid length parameter in SUBSTRING function'. Also, if I change WHERE clause to (WHERE Nodes.SysName <> Nodes.DNS) it also throws an 'Invalid length parameter in SUBSTRING function' (again, not the result I'm looking for). I don't understand why SUBSTRING is throwing a length error in the WHERE clause but works fine when used in column selection.

Any advice how to get this SQL query to work to be able to compare the two aliased columns?

SELECT TOP 10

Nodes.Caption

, Nodes.SysName

, Nodes.DNS

, SUBSTRING(Nodes.SysName, 1, CHARINDEX('.', Nodes.SysName) - 1) AS SysName_hostname

, SUBSTRING(Nodes.DNS, 1, CHARINDEX('.', Nodes.DNS) - 1) AS DNS_hostname

FROM Nodes

WHERE Nodes.Caption <> Nodes.SysName

Find more posts tagged with

report_writer

report

Accepted answers

LGarvin

Ahhh.. so for the rows where CHARINDEX is returning a result of zero, you'll need to explicitly test for that. The conversion error is because neither SysName or DNS are numeric values.

There's a couple of ways you can test for that. In addition to the IS NOT NULL test, you could also explicitly test for the empty string (e.g. Nodes.Sysname <> '')

or you could explicitly exclude any scenario that returns a CHARINDEX of zero (which may include the NULL instances), such as

WHERE CHARINDEX('.',Nodes.SysName)>0 AND CHARINDEX('.',Nodes.DNS)>0

Either way, though, and this is a defect in my original logic, you'll actually have to write this as a nested query, so that the SUBSTRING function is only called on the rows that have non-null/non-zero-length strings.

SELECT N.Caption, N.SysName, N.DNS

FROM (

SELECT Nodes.Caption, Nodes.SysName, Nodes.DNS

FROM Nodes

WHERE CHARINDEX('.',Nodes.SysName)>0 AND CHARINDEX('.',Nodes.DNS)>0

) N

WHERE SUBSTRING(N.SysName, 1, CHARINDEX('.',N.SysName)-1) <> SUBSTRING(N.DNS, 1, CHARINDEX('.',N.DNS)-1)

All comments

njoylif

I've had problems with that before as well.

it should work if you put the whole statement in where clause...

SELECT TOP 10

Nodes.Caption

, Nodes.SysName

, Nodes.DNS

FROM Nodes

WHERE

(SUBSTRING(Nodes.SysName, 1, CHARINDEX('.', Nodes.SysName) - 1) AS SysName_hostname) <> ( SUBSTRING(Nodes.DNS, 1, CHARINDEX('.', Nodes.DNS) - 1) AS DNS_hostname)

FormerMember

Thanks njoylif. SQL didn’t like the ‘AS aliases’ so I removed them but SQL still throws the error ‘Invalid length parameter passed to the LEFT or SUBSTRING function.

WHERE

(SUBSTRING(Nodes.SysName, 1, CHARINDEX('.', Nodes.SysName) - 1) ) <> ( SUBSTRING(Nodes.DNS, 1, CHARINDEX('.', Nodes.DNS) - 1) )

LGarvin

To clarify... SQL does NOT allow the use of SELECT aliases in the WHERE clause. The WHERE clause must be defined with actual database objects.

The reason for this is simple.

The WHERE clause is actually evaluated before the SELECT statement extracts the desired fields from the resulting table,

so as it happens, the AS aliasname references do not actually exist at the time the WHERE clause is applied to the resultset.

I suspect your "Invalid length parameter" is being triggered by the presence of rows in the result set that have NULL, EMPTY, or ZERO-LENGTH values for Nodes.Sysname or Nodes.DNS, which would then be returning a value of ZERO for the CHARINDEX() function, and then the SUBSTRING function falls all over itself trying to process a length parameter of zero.

To further refine this query, include logic to explicitly exclude rows with NULL values in either of those fields, e.g.

WHERE Nodes.SysName IS NOT NULL AND Nodes.DNS IS NOT NULL AND SUBSTRING(Nodes.SysName, 1, CHARINDEX('.',Nodes.SysName)-1) <> SUBSTRING(Nodes.DNS, 1, CHARINDEX('.',Nodes.DNS)-1)

FormerMember

Good thought lgarvin! I hadn’t thought of the possibility of null values. I am aware SQL does not allow aliases in WHERE clause for the reasons you’ve given. That’s what has me in this pickle. In any case, just tried (copy and pasted) your WHERE statement and SQL still threw up again with "Invalid length parameter" error.

Quick Links

All Categories
Recent Posts
Activity
Unanswered
Groups
Help
Best Of