When can SWQL refer to an Alias

codebender

I'm trying to parse strings and am trying to refer to an alias in the SWQL query.

In the following query, I can refer to the alias IP without issue.  However, the alias from the CHARINDEX expression gives an exception: Cannot resolve property P1.

SELECT N.IPAddress AS IP

     , N.DnsBackward

     , CHARINDEX('.',IP,1)-1 AS P1

     , ToString(P1)

FROM IPAM.IPNode AS N

Is this a bug?  If not, what determines when an alias can be referenced?  Is there another way to accomplish this?

tom.rybka

The rules are generally the same as in SQL, i.e. you can only reference alias in the ORDER BY clause. Thus, the P1 cannot be referenced in the ToString function in the SELECT clause and the error is correct. The reference to IP in the CHARINDEX looks wrong, does it pass even if you deleted the ToString expression from the SELECT clause? If it still passed, that would be a bug (although it still may work and result in the correct data, but I recommend not to rely on it; it is undocumented and may be fixed in future).