Report - Where Clause

andy71

Hi could someone please help this SQL novice and provide a little guidance on where I'm going wrong?

I have inherited a deployed system and was trying to apply a filter to the SQL below by adding a where clause (based on the content of the on the caption or NodeName field)

when I add this to the report : -->   Where NodeName like 'INSERT-SOME-VALUE'

a "Query is not valid is received"

has anyone have any insights as to what is causing the error?

thanks in advance

Andy

        DECLARE @StartDate DateTime
        DECLARE @EndDate DateTime

        SET @StartDate = ${FromTime}
        SET @EndDate = ${ToTime}

        SELECT Interfaces.InterfaceId,
        Nodes.NodeID,
        Nodes.Caption AS NodeName,
        Interfaces.Caption AS Interface_Caption,
        Maxbps_In90,
        Maxbps_Out90,
        Maxbps_In95,
        Maxbps_Out95,
        Maxbps_In99,
        Maxbps_Out99
        FROM Nodes
        INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
        INNER JOIN (
        SELECT InterfaceID,
        dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,90) AS Maxbps_In90,
        dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,90) AS Maxbps_Out90,
        dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_In95,
        dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_Out95,
        dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,99) AS Maxbps_In99,
        dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,99) AS Maxbps_Out99
        FROM InterfaceTraffic
        WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate
        GROUP BY InterfaceID
        ) TrafficStat
        ON Interfaces.InterfaceID = TrafficStat.InterfaceID

mesverrum

SQL does not allow you to use an alias inside the Where conditions, use Nodes.Caption instead of nodename