9 Replies Latest reply on Nov 13, 2013 3:29 PM by rgward

    SQL Help?

    rgward

      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

        • Re: SQL Help?
          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)

            • Re: SQL Help?
              rgward

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

                • Re: SQL Help?
                  Lawrence Garvin

                  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)

                    • Re: SQL Help?
                      rgward

                      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.

                        • Re: SQL Help?
                          Lawrence Garvin

                          In any case, just tried (copy and pasted) your WHERE statement and SQL still threw up again with "Invalid length parameter" error.

                           

                          Okay, let's try this. Let's make sure we're getting valid values for the clauses in the WHERE query.

                           

                          Try running this query. Let's see what you get:

                           

                          SELECT Nodes.SysName, CHARINDEX('.', Nodes.SysName), SUBSTRING(Nodes.SysName, 1, CHARINDEX('.',Nodes.SysName)-1) FROM NODES

                           

                          SELECT Nodes.DNS, CHARINDEX('.', Nodes.DNS), SUBSTRING(Nodes.DNS, 1, CHARINDEX('.',Nodes.DNS)-1) FROM NODES

                            • Re: SQL Help?
                              rgward

                              Both queries throw the same error "Invalid length parameter".

                              • Re: SQL Help?
                                rgward

                                LGarvin, you have me focusing in the right direction now.  I removed the SUBSTRING(Nodes.SysName, 1, CHARINDEX('.',Nodes.SysName)-1) part and ran the queries. The results reveal several rows for each column with a CHARINDEX result of 0.  I see several rows where Nodes.SysName and Nodes.DNS columns are blank and thus return 0 value. Not sure why right now and need to research.  I made a tweek to your original WHERE clause suggestion and made progress but not the success I was hoping for.

                                 

                                WHERE (Nodes.SysName <> 0 OR Nodes.DNS <> 0) AND SUBSTRING(Nodes.SysName, 1, CHARINDEX('.',Nodes.SysName)-1) <> SUBSTRING(Nodes.DNS, 1, CHARINDEX('.',Nodes.DNS)-1)


                                Now getting 'Conversion failed when converting nvarchar value 'MAAASHBACKUP' to data type int.'.  'MAAASHBACKUP' is a valid value in Nodes.SysName.column with a correct CHARINDEX value returned of 13.  Now I need to figure out why I'm getting this error on this particular record.  Any thoughts?   In any case, you have been a great help in helping me work this out.  Thank you!

                                  • Re: SQL Help?
                                    Lawrence Garvin

                                    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)