This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Using Common Table Expressions (CTE) in SQL Reports

Is there a way to use Common Table expressions in my custom SQL reports?  It does not appear so, but maybe I'm missing something.  I'm trying something like this, which works in SQL management studio, but not when adding the query to the report.

WITH    Counts ( C )
          AS ( SELECT   COUNT(*) C
               FROM     ( ( ( Nodes
                              INNER JOIN Interfaces ON ( Nodes.NodeID = Interfaces.NodeID )
                            )
                            INNER JOIN CustomPollerAssignment ON ( Nodes.NodeID = CustomPollerAssignment.NodeID
                                                              AND Interfaces.InterfaceID = CustomPollerAssignment.InterfaceID
                                                              )
                          )
                          INNER JOIN CustomPollers ON ( CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID )
                        )
                        INNER JOIN CustomPollerStatus ON ( CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID )
               WHERE    ( ( ( CustomPollers.UniqueName = 'cpaePortCapabilitiesEnabled'
                              AND CustomPollerStatus.Status LIKE '0%'
                            )
                            OR ( CustomPollers.UniqueName = 'hpicfUsrAuthMacAuthAdminStatus'
                                 AND CustomPollerStatus.Status = '2'
                               )
                            OR ( CustomPollers.UniqueName = 'jnxPaeAuthConfigMacAuthStatus'
                                 AND CustomPollerAssignment.Status = 0
                                 AND InterfaceType = 53
                               )
                          )
                          AND ( Nodes.Location = SWITCH' )
                          AND NOT ( Interfaces.Caption LIKE '%Router%'
                                    OR Interfaces.Caption LIKE '%Switch%'
                                    OR Interfaces.Caption LIKE '%Uplink%'
                                    OR Interfaces.Caption LIKE '%Exception%'
                                  )
                          AND InterfaceType IN ( 6 )
                          AND 1 = 1
                        )
               UNION
               SELECT   COUNT(*) C
               FROM     ( ( ( Nodes
                              INNER JOIN Interfaces ON ( Nodes.NodeID = Interfaces.NodeID )
                            )
                            INNER JOIN CustomPollerAssignment ON ( Nodes.NodeID = CustomPollerAssignment.NodeID
                                                              AND Interfaces.InterfaceID = CustomPollerAssignment.InterfaceID
                                                              )
                          )
                          INNER JOIN CustomPollers ON ( CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID )
                        )
               WHERE    ( ( (CustomPollers.UniqueName = 'jnxPaeAuthConfigMacAuthStatus'
                            AND CustomPollerAssignment.Status = 0
                            AND InterfaceType = 53
        
                          )
                          AND ( Nodes.Location = 'SWITCH' )
                          AND NOT ( Interfaces.Caption LIKE '%Router%'
                                    OR Interfaces.Caption LIKE '%Switch%'
                                    OR Interfaces.Caption LIKE '%Uplink%'
                                    OR Interfaces.Caption LIKE '%Exception%'
                                  )
                        )
             )
    SELECT  SUM(C) Num_Disabled_ports
    FROM    Counts;

Parents Reply Children